Menu
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?
762
You can use both the MIN and MAX functions in one SELECT . If you use only these functions without any columns, you don't need a GROUP BY clause.
While all aggregate functions could be used without the GROUP BY clause, the whole point is to use the GROUP BY clause. That clause serves as the place where you'll define the condition on how to create a group. When the group is created, you'll calculate aggregated values.
Max() function with Group by In this page we are discussing, how the GROUP BY clause along with the SQL MAX() can be used to find the maximum value of a column over each group. 1.
MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
