MAX function without group by

Question

I have the following table:

ID | NUM
 1 |  4
 2 |  9
 3 |  1
 4 |  7
 5 |  10

I want a result of:

ID | NUM
 5 | 10

When I try to use MAX(NUM) I get and error that I have to use GROUP BY in order to use MAX function

Any idea?

Answer 1

As per the error, use of an aggregate like Max requires a Group By clause if there are any non-aggregated columns in the select list (In your case, you are trying to find the MAX(Num) and then return the value(s) associated in the ID column). In MS SQL Server you can get what you want via ordering and limiting the returned rows:

SELECT TOP 1 ID, NUM 
FROM [table] 
ORDER BY NUM DESC;

In other RDBMS systems the LIMIT offers similar functionality.

Edit

If you need to return all rows which have the same maximum, then use the WITH TIES qualification:

SELECT TOP 1 WITH TIES ID, NUM 
FROM [table] 
ORDER BY NUM DESC;