CODEWITHSUNDEEP
sqlsql-serverselect
mike
mike

Reputation: 302

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

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.

Upvotes: 11

Views: 57575

Answers (4)

Code Magician
Code Magician

Reputation: 23972

What about 

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

See:

Upvotes: 24

flesk
flesk

Reputation: 7579

It's not as hard as you'd think.

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

Upvotes: 9

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

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

Upvotes: 7

Marc B
Marc B

Reputation: 360572

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

Upvotes: 6

Related Questions