SQL: how to choose the maximum value for each group per day?

Question

Say I have a table with the following columns ...

Name, Date, Number 

And let's say that the following data was entered into these columns ...

 Bob, 2011-11-22, 1 Bob, 2011-11-22, 5 Bob, 2011-11-22, 4 Bob, 2011-11-22, 3 Wendy, 2011-11-22, 3 Wendy, 2011-11-22, 4 Wendy, 2011-11-22, 2 Wendy, 2011-11-22, 1 Chris, 2011-11-22, 4 Chris, 2011-11-22, 1 Bob, 2011-11-21, 4 Bob, 2011-11-21, 3 Wendy, 2011-11-21, 2 Wendy, 2011-11-21, 4 Wendy, 2011-11-21, 1 Chris, 2011-11-21, 4 Chris, 2011-11-21, 1 

Now what I would like to do is get the Max Number value for each name for each date. So my query result will look like this:

 Bob, 2011-11-22, 5 Wendy, 2011-11-22, 4 Chris, 2011-11-22, 4 Bob, 2011-11-21, 4 Wendy, 2011-11-21, 4 Chris, 2011-11-21, 4 

Any help would be greatly appreciated. I am using SQL 2005.

Answer 1

What about

 SELECT [Name], [Date], MAX(Number) FROM [yourTable] GROUP BY [Name], [Date] 

See:

• GROUP BY (Aggregate) Functions
• MySQL GROUP BY - Aggregate Functions

Answer 2

It is not as difficult as you think.

 select name, date, max(number) from table group by name, date 

Answer 3

 SELECT Name, Date, MAX(Number) FROM YourTable GROUP BY Name, Date; 

Answer 4

 SELECT Name, `Date`, MAX(Number) FROM yourtable GROUP BY Name, `Date`