[SOLVED] GROUP BY sorting using MAX(): how to get rest of MAX(result) row?

[batch]

I have two tables: members and table2. Here is an example of the contents:

 

members

myid hours_played

111  1.5

222  0.8

333  2.4

 

table2

myid nickname    date

111  peter          1-1-2009

222  john          2-2-2009

222  johnnyboy  3-3-2009

333  joe            4-4-2009

 

I think the tables are fairly obvious: one member can use different nicknames, but can have only one account for which information is stored in the members table.

 

 

What I want to do: select the most recent record for every member.

Problem: by using MAX(table2.date) I do indeed get the most recent date for this particular member (myid), but the rest of the result returned for this member doesn't correspond to the row from which the MAX(date) was obtained. For example I'd get something like this:

 

222  john  3-3-2009 <- notice this is the most recent date, but the nickname field belongs to the older record!

 

I am expecting to get this:

 

222  johnnyboy  3-3-2009 <- notice how the nickname field is part of the same record as the date

 

This is my query:

SELECT members.myid, table2.nickname, MAX(table2.date) AS date

FROM members, table2

WHERE  members.myid = table2.myid

GROUP BY members.myid

 

 

[Maq]

Your date field should be a TIMESTAMP.

 

You also need an ORDER BY.  You're getting the correct date because you select the MAX date but everything else is the record order.

[batch]

Your date field should be a TIMESTAMP.

 

You also need an ORDER BY.

 

The date field is DATETIME, which should be sufficient. Also, the original query is much longer but I only mentioned the relevant details I already:

 

ORDER BY members.hours_played

 

in the real query. I just need to get the most recent nickname+date used by a member using a single query. So whatever record MAX(date) belongs to, that's the row I want returned!

[batch]

Although I'm disappointed with the lack of responses, I ended up finding a great article on how to solve this very issue [ here ]. Although the instructions are for sorting a single table, you can easily join a second (different) table in the query like so:

 

SELECT t2a.nickname, t2a.date, m1.myid, m1.hours_played

FROM table2 AS t2a, members AS m1, (

 

SELECT myid, MAX( date ) AS t2b_newestdate

FROM table2

GROUP BY myid

) AS t2b

WHERE t2b.myid = t2a.myid

AND t2b_newestdate = t2a.date

AND t2b.myid = m1.myid

 

The last line ensures that we will get the proper record from the members table. The two lines before that match the row that contains the unique MyID/Date combination that was returned for the most recent date. Using that information we can then finally select the specific record that contains the most recent date field for this MyID. 

[fenway]

There must have been countless examples on this very topic on this board... and that link is even in one of the stickies.  Glad you figured it out.