SQL max function get other tables

user_6346482 0
Viewed 25

I have a simple relation "sea" with only two columns, one is called "name" and the other "depth". With the following command, I can output the number maximum number in the attribute depth:

SELECT max(depth) FROM sea;

I am trying to get the the name of the maximum depth as well, such that it outputs:

    name | depth
___________________
pacific  | 11034

Is there a way to output that as well?

I have tried it with group by and also by trying to JOIN the table with itself receiving the other attributes, that did not find any solution.

aggregate-functions max join postgresql sql
1 Answers

Use order by:

select s.*
from sea s
order by s.depth desc
fetch first 1 row only;

Note: Some databases do not support fetch -- the standard SQL syntax -- so you might use limit or select top (1) or some similar construct.

The above returns exactly one row, even if there are ties. If you want all rows, then a simple method is a subquery:

select s.*
from seas s
where s.depth = (select max(s2.depth) from seas s2);
user_11440350
Related