Reputation: 2134

SQL max() only returns 1 row if column has several max values

This feels like a stupid question since it should have a simple answer, but I just can't find it. I have a table looking like this:

|-----|---|
|  a  | b |
|-----|---|
| ALA | 2 |
| ASP | 1 |
| SER | 1 |
| VAL | 2 |
|-----|---|

What I need is to get the two rows with the maximum value (I don't know the values in advance) which means that my example above shoud give:

|-----|--------|
|  a  | max(b) |
|-----|--------|
| ALA |   2    |
| VAL |   2    |
|-----|--------|

I'm trying

SELECT a, max(b) FROM table;

but it only gives the first maximum row:

|-----|--------|
|  a  | max(b) |
|-----|--------|
| ALA |   2    |
|-----|--------|

What do I miss?

Upvotes: 4

Answers (3)

Reputation: 32693

If you use SQL Server, you can use TOP WITH TIES, which should be more efficient than subquery or RANK.

SELECT TOP(1) WITH TIES 
    a, b
FROM YourTable
ORDER BY b DESC;

Other databases may also have similar option.

Upvotes: 0

Reputation: 48197

SELECT *
FROM YourTable Y
INNER JOIN (
       SELECT Max(b) mB
       FROM YourTable
       ) M
On Y.b = M.mb

Also if you have sql server 2008+ / oracle you can use something like RANK

SELECT *
FROM ( 
       SELECT a, b, RANK() over (order by B DESC) rn
       FROM YourTable
     ) T
WHERE T.rn = 1

Upvotes: 0

Reputation: 4053

SELECT a,b FROM table
WHERE b = (SELECT MAX(b) FROM table)

Upvotes: 9