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

Question

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?

• Rubens Farias: SELECT a, max(b) FROM table should generate an error, as you used an aggregate function and missed the GROUP BY clause
• Parfait: @RubensFarias, unfortunately MySQL allows this non-ANSI compliant syntax and users continually avail themselves of this at the detriment of their learning process and application.

Answer 1

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

Answer 2

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

• Vladimir Baranov: I think, that TOP WITH TIES is much simpler than RANK and more efficient.
• Juan Carlos Oropeza: @VladimirBaranov Looks like you have a point, even when I hear WITH TIES before still not very familiar with that one. Anyway OP didnt specify Sql Server`` as rdbms, I just put it because came to my mind.
• Vladimir Baranov: Yes, I just wanted to point that there is such option and it is rarely used, though it can be quite useful.

Answer 3

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.