I have an interesting situation with GROUP BY. I think it's best
illustrated by example (I'm doing this toy example by hand, so forgive
me if I mess up the syntax):

id name msg time
-----------------------------------
1 alice foo 1
2 bob bar 2
3 claire foo 3
4 deborah bar 4
5 edgar foo 5

What I want to do is to collapse the 'msg' columns together so I only
get one result for each unique msg value. I want the results sorted by
'time', so that items with the highest times are located at the top of
the list. It seemed like the natural way to do this is with a GROUP BY
statement.I execute a query that looks like this

SELECT *,max(time) FROM table GROUP BY msg ORDER BY max(time) DESC

The results might look like this:

id name msg time max(time)
-----------------------------------------------------
1 alice foo 1 5
2 bob bar 2 4

The sorting worked out correctly, so msg=foo (with time 5) ended up at
the top. However, what I wanted was for 'id' and 'name' to correspond
to the record with the maximum time. Something like this:

id name msg time max(time)
-----------------------------------------------------
5 edgar foo 5 5
4 deborah bar 4 4

I think the problem is that the SQL server does the GROUP BY first
(the collapsing down the duplicate entries) and then does the ORDER
BY. What I want is the data sorted first and then grouped. I get a
syntax error if I try to put the ORDER BY before the GROUP BY. Is
there a way to do this?

I'm using MySQL for my database

Thanks,
Scott

I seem to have come up with the reasonable solution that involves
joining the results of the query back into the database, selecting the
rows that matched the highest timestamp.

original_query = SELECT *,max(time) as maxtime FROM table GROUP BY msg
ORDER BY max(time) DESC

then substitute original_query into the following:

SELECT x.*,y.id as maxid FROM (original_query) as x inner join table
as y on x.msg=y.msg and x.maxtime=y.time

The only issue here is that I occasionally get duplicates because the
time field isn't unique (consider if there was a Frank who also had
time=5, then we'd get two rows for msg=foo)

Scott <smbaker (AT) gmail (DOT) com> wrote:

<snip>

This is an FAQ: "How do I find the groupwise maximum?"
Google it.


XL