MAX without a group

Question

I have the following table:

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

I want to get the result:

 ID | NUM 5 | 10 

When I try to use MAX (NUM), I get an error that I have to use GROUP BY to use the MAX function

Any idea?

Answer 1

According to an error, using an aggregate of type Max requires a Group By clause if there are any non-aggregated columns in the selection list (in your case, you are trying to find MAX(Num) and then return the values ​​associated in the ID column). In MS SQL Server, you can get what you want by organizing and restricting the returned rows:

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

In other RDBMS systems, LIMIT offers similar functionality.

Edit

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

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

Answer 2

Try this request.

  WITH result AS ( select DENSE_RANK() OVER( ORDER BY NUM desc) AS RowNo,ID,NUM from #emp ) select ID,NUM from result where RowNo=1 

it will return maximum values ​​even if it has more MAX values, for example:

 ID | NUM 5 | 10 6 | 10 

see the link below to learn more about the RATING features:
http://msdn.microsoft.com/en-us/library/ms189798

Answer 3

May return more than 1 result:

 SELECT id, num FROM table WHERE num = (SELECT MAX(num) FROM table) 

Answer 4

What about:

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

Answer 5

Do it -

 SELECT TOP 1 ID, NUM FROM <yourtable> ORDER BY NUM DESC; 

Answer 6

Get all rows have maximum values, but THERE ARE 3 SELECT, this is not suitable for performance

 SELECT id, MAX(num) as num FROM table GROUP BY id ORDER BY MAX(num) DESC LIMIT (SELECT COUNT(*) FROM table WHERE num =(SELECT MAX(num) FROM table) )