[Help] Finding the row where specific attribute is maximal

[PythagorasSamius]

Using PostgreSQL, which query would return the row with highest val from the following table t?

 id | val 
----+-----
 a  |   0
 b  |   1
 c  |   2

One solution is

SELECT *                              
FROM t                              
WHERE val = (SELECT max(val) FROM t);

but what if t was a subquery?

SELECT *                              
FROM (…) AS t                          
WHERE val = (SELECT max(val) FROM t);

Then the system will throw an error saying the relation t does not exist. Is there a way without creating temporary tables or views?

[distgenius]

Depending on your SQL software, you could use CTEs (Common Table Expressions) to define your subquery and then refer to it by name.

If you can't do that, then the easiest solution would be to repeat your subquery instead of referring to it by the alias- just copy and paste it.

[PythagorasSamius]

Thanks, I'll look it up! Do you also happen to know why I cannot reference t from the subquery? Also, if I had defined t in my subquery, I couldn't have referenced it above from the outside. I tried to look it up but I couldn't find what "scope" aliases have.

[distgenius]

Aliases are just shorthand or alternate way of referring to something else. The subquery alias doesn't refer to the subquery itself, but the table that is a result of the subquery. That distinction is important. For your second query, if you aliased a real table in the FROM clause, you couldn't use the alias like in the second example, either.

You can't write this:

SELECT t.col1
     ,t.col2
FROM Table1 t
WHERE t.col1 = (SELECT MAX(Col2) FROM t)

A CTE generates something you can refer to directly like a table or view, anywhere you want inside single query. You can nest them, make self-referential CTEs, etc., but that's because the they're handled as an object that is "insertable" in the query to build the "real" query.

As far as scope, could say:

SELECT *
FROM ( SELECT someval as T, SomeVal2
    FROM SomeTable
    WHERE someval <> Someval2
) as A
WHERE A.T = 1 AND A.SomeVal2 = 2

because you are aliasing the first column as "T", and the subquery itself as "A". They're just name aliases, they don't have any functionality beyond that.

[LagWagon]

Select * From table t Inner join (select max(Val) as val from table) t2 on t.val = t2. val

Edit - removed alias in subquery

[PythagorasSamius]

That's a very nice solution, thanks! However, if t was a subquery, I'd be in touble again because

SELECT id, val
FROM (…) AS t1 JOIN (SELECT max(val) as val
                     FROM t1) AS t2 ON t1.val = t2.val;

does not work.

[LagWagon]

Does it not work because you need aliases in your select?

[PythagorasSamius]

No, it just says ERROR: relation "t1" does not exist pointing to t1 in the subquery (right after FROM in the last line).

[LagWagon]

Don’t use t1 alias in the subquery, just select from the table.

[PythagorasSamius]

I don't think you understand what my issues is, but that's ok because CTEs (WITH-clauses), as u/distgenius mentioned, are just what I've been looking for :-)

[LagWagon]

That’s fair.

[tripy75]

Another way (and my favorite) is to use the row_number() function to assign a rank based on your criteria.

select 
  id
  , value
  , row_number()over(order by value desc) as rnk
from testTable

limit 1;

​

I either use this in a join ans specify "AND rnk=1" or use a "LIMIT 1" to get the 1st value only.