How to select the row with the minimum value of a column while the other column is at its maximum?-CodePudding

In SQL, how to select the minimum value of a column a (integer) where the column b (integer) is maximum, using only one select ?

SELECT MIN(a) OVER (PARTITION BY MAX(b)) as res #seems to be ill-formed
FROM table

For instance

should return

res
1

CodePudding user response：

order by and limit should be good enough here:

select a
from mytable
order by b desc, a
limit 1

CodePudding user response：

You can do it with a nested SELECT MIN

SELECT MIN(a) FROM table WHERE b = (SELECT MIN(b) FROM table);

CodePudding user response：

Using windowing functions to avoid a second pass on the table:

select min(a)
  from (select a,
               b
               MAX(b) OVER (PARTITION BY 1) max_b
          from table)
  where b = max_b