Tricky GROUP BY Statement

dirkoneill

Ok this is going to seem a little confusing at first but i will will explain my question as best as possible. I have a list of log entries and each entry has an actionin the database.

Logs:

Action - Date

ADD - 2/17/2004
DEL - 2/1/2004
EDIT - 1/23/2004
ADD - 1/23/2004

What i want to return on a query is a list of each action that has occured but only the most recent action. The natural though was to do 'GROUP BY Action ORDER BY Date DESC'. This is the desired result is:

Action - Date

ADD - 2/17/2004
DEL - 2/1/2004
EDIT - 1/23/2004

what is returning is this:

Action - Date

DEL - 2/1/2004
EDIT - 1/23/2004
ADD - 1/23/2004

I cannot seem to get the GROUP BY function to return the most recent action. it seems to take it's first hit in the database and store that as it's comparitive value. I could have it return every action in the log and then have php print a result then exit out of the loop if the action is the same as the previous. But we all know that after a good point of time this will slow the system down when it must search through thousands of records. Thanks for the help.

LordShryku

Assuming your date field is in a date format of some kind, you should be selecting max(date)...

  SELECT action,
         max(date)
    FROM table
GROUP BY action

dirkoneill

fantastic. thanks so much. i knew it had to be something simple like that.

dirkoneill

ok i found another problem. there is other data other then action and date like the actuall message. The problem is that the correct date (most recent) is being selected but the message that is returned is from the earlier date (first action). so this isn't exactly the correct work around.

LordShryku

Show me your query. This isn't a workaround. This is the correct way to do it.

Sxooter

There are a few ways to do it, but as soon as you start doing funky things with group by you often need subqueries to make it do what you want.

SELECT action,
max(date)
FROM table
GROUP BY action

The first temptation is to just grab message, which mysql will let you do (though it shouldn't, for SQL compliance, you have to use an aggregate function on an item in the select list, or group by it.)

select action,
message,
max(date)
from table
group by action

This is not a valid query, and even if MySQL will run it, you'll likely be getting the wrong message, as it isn't guaranteed you'll get the one that goes with the same record as the one for that action (i.e. you could get the same message from one action for all the actions you get back.)

Now, we have a few issues to consider. Will we have repeating dates in this action? i.e. can we have two adds on 11-mar-2004? if so, which message do we want? Both, the last?