CODEWITHSUNDEEP
mysqlsql
Ragnar Lothbrok
Ragnar Lothbrok

Reputation: 13

Find all highest values of a column in an SQL table

I would like to select all the rows with the highest number, which is 293 in this case. However, it could be any number as I don't know it up front. So a simple where clause won't be sufficient. I already tried something with MAX() but that only took 1 row while I need all of them. Can anyone help me?

ID     NUMBER
1       293
2       293
3       293
4       148
5       96

Upvotes: 0

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

A typical method is to use window functions:

select id, number
from (select t.*, max(number) over () as maxn
      from t
     ) t
where number = maxn;

Upvotes: 2

Lamak
Lamak

Reputation: 70668

This is very basic:

SELECT *
FROM YourTable
WHERE NUMBER = (SELECT MAX(NUMBER) FROM YourTable);

Upvotes: 4

Related Questions