How can I use an alias inside a subquery?

Question

I am trying to get the rows to have the maximum value from a table like this:

A | B | Ratio 
0 | 1 | 1.0
1 | 2 | 1.0
2 | 3 | 1.0
3 | 4 | 0.33
4 | 5 | 0.25

I am trying to display only rows containing the maximum value (in this case 1.0). Maybe I'm not doing it right. I have a form request:

SELECT A,B,C 
FROM (---Long Sub Query--- Aliased as Full_Table)
WHERE RATIO=(SELECT MAX(RATIO) FROM Full_Table);

But Full_Table cannot refer to the second subquery. There are several rows with the same maximum value, so I used this query. Is there a better design to achieve this? In the worst case, I have to replace the second Full_Table with the entire long request, but I hope there is a better way to do this.

Answer 1

You can use the expression Common Table:

WITH Full_Table AS (---Long Sub Query---)
SELECT A,B,C 
FROM Full_Table
WHERE RATIO=(SELECT MAX(RATIO) FROM Full_Table);

Answer 2

:

SELECT full_table.a,
       full_table.b,
       full_table.c
  FROM (SELECT ...,
               RANK() OVER (ORDER BY ratio DESC) AS rank
          FROM Sub Query---) full_table
 WHERE full_table.rank = 1

, , RANK(), ROW_NUMBER(), ROW_NUMBER .

WITH, / ...

Oracle 9i + WITH, " ". , SQL Server 2005+, WITH (CTE). SQL Server WITH Oracle 9i-11g - Oracle WITH ( ANSI) 11g R2, - , Oracle CONNECT BY Oracle v2). WITH - / - .

WITH full_table AS (
  SELECT...)
SELECT x.a, x.b, x.c
  FROM full_table x
  JOIN (SELECT MAX(t.ratio) AS max_ratio 
          FROM full_table t) y ON y.max_ratio = x.ratio

... :

SELECT x.a, x.b, x.c
  FROM (SELECT ...) x
  JOIN (SELECT MAX(t.ratio) AS max_ratio 
          FROM (SELECT ...) t) y ON y.max_ratio = x.ratio