Question 21504 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

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

For instance

a b
1 1
1 3
2 3

should return

res
1
Tags:

3

Answers


  1. 0

    order by and limit should be good enough here:

    select a
from mytable
order by b desc, a
limit 1
    Login or Signup to reply.
  2. 0

    You can do it with a nested SELECT MIN

    SELECT MIN(a) FROM table WHERE b = (SELECT MIN(b) FROM table);
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search