CODEWITHSUNDEEP
sqlsql-server
m0fo
m0fo

Reputation: 2189

MAX function without group by

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?

Upvotes: 18

Views: 85099

Answers (6)

StuartLC
StuartLC

Reputation: 107267

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;

Upvotes: 18

Siva
Siva

Reputation: 79

Try this query.

    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 max values even if it has more MAX values like:

ID | NUM
 5 | 10
 6 | 10

refer below link to know more about RANKING Functions:
http://msdn.microsoft.com/en-us/library/ms189798

Upvotes: 7

user2506280
user2506280

Reputation: 11

Get all rows have max values but THERE ARE 3 SELECT, It's not good 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)
       )

Upvotes: 0

jpiasetz
jpiasetz

Reputation: 1772

How about:

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

Upvotes: 2

JHS
JHS

Reputation: 7871

Do this -

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

Upvotes: 1

Glenn
Glenn

Reputation: 9150

May return more than 1 result:

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

Upvotes: 8

Related Questions