Is there a way to select a max row for each group in SQL?

[sciwizam]

I need to display the entire row for the maximum value in a group

For example,

Time                 Name     Type    Quantity
-----------------------------------------------
2010-05-01 08:00:00    John     Apple    20
2010-05-01 12:00:00    John     Plum     25
2010-05-01 14:00:00    John     Lime     19
2010-05-01 16:00:00    John     Apple    22
2010-05-01 17:00:00    John     Cherry   24
2010-05-01 19:00:00    John     Pear     19
2010-05-01 08:00:00    Bob      Cherry   29
2010-05-01 12:00:00    Bob      Lemon    22
2010-05-01 14:00:00    Bob      Pear     14
2010-05-01 16:00:00    Bob      Mango    20
2010-05-01 17:00:00    Bob      Banana   21
2010-05-01 19:00:00    Bob      Apple    27

Something like the above with 10 names and the for over 1 month.

What I would like to see is

Time                 Name     Type    Quantity
-----------------------------------------------
2010-05-01 08:00:00    John     Plum     25
2010-05-01 19:00:00    Bob      Apple    27
...
...
...
...
2010-05-31 12:00:00    John     Cherry   32
2010-05-31 14:00:00    Bob      Apple    44
...
2010-05-31 16:00:00    Stan     Banana   35

Is this possible?

 

[BoberFett]

Sure it's possible.

If you're using SQL Server 2005, look up the PARTITION BY clause.

For other servers, you can use a subquery with GROUP BY to get the max value per group, then join back to the original table.


Edit: Why do you want Bob Apple 24 to show in your result set? If I'm understanding correctly, you only want Bob Apple 44 to show up? Your example data is hard to understand, I can't tell how it's being generated without more information. What is your query to get the unfiltered data to begin with, then what is the desired GROUP BY to pick the top 10?

 

[sciwizam]

Sorry, you are correct. I messed up in the example resultset. I've corrected it now. It should be Bob Apple 27.

My data is some network traffic info across several locations. Each location gets polled at a certain interval. What I want is to basically have 1 row for each location for each day. By choosing the max of 1 network metric, and using the whole row as that day's data for that location. BTW, I'm using MySQLQuery Browser to look up the data and I'm pretty sure I don't have any edit permissions and I'm not really familiar with any DB stuff. Just starting to learn MySQL.

 

[hiddensniper11]

"Select Date, Location, MAX(quantity) from XXX Group by Date, Location"

I think this should get you your desired results.

 

[BoberFett]

GROUP BY DATE is often going to fail to give you what you want because it includes times as well. There are probably almost as many unique dates as there are rows (depending on how the dates were written) so a GROUP BY DATE will pull almost everything. In addition, he wants the entire row, not just the grouped and aggregate data.

So the subquery route is:

select t.*
from table t
join (select name, type, max(quantity) as maxqty from table where time >= #starttime and time < #endtime group by name, type) sq on t.name = sq.name and t.type = sq.type and t.quantity = sq.maxqty

At least that's what I think should do it, without setting up a table and actually running it. Mind you that this approach will only get you the top 1 because the aggregate in the subquery only returns a single value per group. If you wanted, say, the top 10 of each group, there'd be quite a bit more wrangling involved.

 

[KIAman]

You can group by date only by using a simple convert statement.

 

[BoberFett]

I guess it depends on whether or not he will always be searching for calendar months or whether he wants to have variable date ranges.

 

[nakedfrog]

Maybe a scalar function? As in, select distinct username, fn_get_max_quantity(username)?

 

[DT4K]

select y.time, y.name, y.type, y.quantity
from (
select time, name, max(quantity) as maxqty from yourtable
group by time, name
) as x inner join yourtable as y 
on x.name = y.name and x.maxqty = y.quantity 
and x.time = y.time

I think that would work if all your dates were only dates without any time values and you wanted to get a list of the records with highest quantity for each person for each day.

To deal with the times, you'd have to do something different and it would depend on the database you are using. Here is a query that works in SQL Server. But you'd have to do something different in mySQL.

select y.[time], y.name, y.type, y.quantity
from (
select cast(floor(cast([time] as float))as datetime) as [time] , 
name, max(quantity) as maxqty from @yourtable
group by cast(floor(cast([time] as float))as datetime), name
) as x inner join @yourtable as y 
on x.name = y.name and x.maxqty = y.quantity 
and 
cast(floor(cast(x.[time] as float))as datetime) = cast(floor(cast(y.[time] as float)) as datetime)

Data in the table:

time                     name    type    quantity
2010-03-19 10:33:12.000    John    Apple    20
2010-03-19 17:32:11.000    John    Plum    25
2010-03-19 10:32:21.000    John    Lime    19
2010-03-19 11:03:23.000    Bob    Apple    27
2010-03-19 12:00:12.000    Bob    Lemon    20
2010-03-19 15:00:00.000    Bob    Pear    5
2010-03-17 01:32:23.000    John    Apple    20
2010-03-17 05:08:12.000    John    Plum    23
2010-03-17 03:08:17.000    John    Lime    22
2010-03-17 12:32:18.000    Bob    Apple    38
2010-03-17 11:32:12.000    Bob    Lemon    20
2010-03-17 22:32:10.000    Bob    Pear    5

Results of my query:

time                     name    type    quantity
2010-03-19 17:32:11.000    John    Plum    25
2010-03-19 11:03:23.000    Bob    Apple    27
2010-03-17 05:08:12.000    John    Plum    23
2010-03-17 12:32:18.000    Bob    Apple    38

You might run into issues with that query if you have multiple records for the same person, type, and quantity on the same day because the join is being done on date only, not date and time.

 

[Sgraffite]

You can do something like this:

SELECT t.time, t.name, t.type, max(t.quantity) AS max_quantity
FROM yourTable t
WHERE
t.time BETWEEN '2010-03-01 00:00:00' AND '2010-03-31 23:59:59'
GROUP BY t.type

I wasn't sure what you meant about the month so I just made something up in the query. You could also group by month easily if you would like. Also the time column should probably be named date or date_time.

That should give you distinct types with only the max of each.

 

[DT4K]

You can do something like this:

SELECT t.time, t.name, t.type, max(t.quantity) AS max_quantity
FROM yourTable t
WHERE
t.time BETWEEN '2010-03-01 00:00:00' AND '2010-03-31 23:59:59'
GROUP BY t.type

I wasn't sure what you meant about the month so I just made something up in the query. You could also group by month easily if you would like. Also the time column should probably be named date or date_time.

That should give you distinct types with only the max of each.

Unless I'm misunderstanding, the OP doesn't want the max for each type. He wants the max for each person for each day, regardless of what type it is. If you didn't care what type had the max, that would be easy to do by simply grouping on day and name and leaving the type out of the group criteria. The complication is that even though he doesn't want to group by type, he still wants to see the type that corresponds to the record with the highest quantity for each person and day.

So my query above groups by day and name, getting the max quantity for all types. Then it joins those results to the table to get the actual record(or records) that match that max quantity for each day and name.