Finding maximum values in a table

Question

What is the simplest way to find the largest value of a field when there are 2 or more results?

I've been playing with the W3Schools site using the orders table. I've tried to display all the rows with the largest employee number (9). Here is the link - http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_avg

I can only figure out how to get it to print out one row. How can I get it to print all of the results?

I tried the following:

select max(EmployeeID)
from orders;

and just get the result of 9. I'm having trouble getting my head around this.

max(EmployeeID) returns the greatest value of EmployeeID in the table. There can only be one maximum number, so 9 is the whole result of that query. Just as there can be only one result for average, as in the sample you linked to. — APC
– APC, Commented Jun 25, 2013 at 10:07

APC · Accepted Answer · 2013-06-25 10:03:47Z

3

MAX() is an aggregate function, and all aggregate functions return only one row.

To get all the rows of the order table which belong to the employee with the highest id use a sub-query this

select *
from orders
where EmployeeID  = (select max(EmployeeID) from orders);

APC

147k20 gold badges180 silver badges289 bronze badges

answered Jun 25, 2013 at 9:38

MaVRoSCy

17.9k15 gold badges87 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1