SQL: How to select a max value for each group per day?

Question

Let's say I have a table that has the following columns...

Name, Date, Number

And say we had the following data inserted 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'd like to do is get the max Number value for each Name, for each date. So my query result would 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 appreciated. I'm 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's not as hard as you'd think.

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