Sorting/Choosing after results returned

[rburch]

I need some experts advice on this one. I've worked on a solution for awhile and am getting no where.

I need to select the newest entry for each id from a transactions table.

Right now each id# can leave multiple records in the table and I am selecting them by "SELECT id, cb FROM $table_name WHERE date <='dateq' AND cb > '#'" (I could group them by id, but I don't see how this would help.)

Now I just need to take it one step more, because this result gives me (or could give me) multiple entries from this table for each id, but I only want to display the latest entry for each id, without knowing what the id's are. I hope that made sesne....

Is this a secondary sort that needs to be done by a function I write or can it be done some other way? If it is a function, any ideas how to write it?

Thanks in advance!!!

[phporcaffeine]

if the "date" column is a timestamp you could just put the results in an array and do a sort() on the timestamp element - the greatest or oldest date would be at the bottom then you could array_shift() the first element on the stack and there is the lowest or "newest" timestamp.


All assuming your using timestamps of course.

[rburch]

I don't think that will work, because I would need to run that "sort()" on each group of id's that are the same. Maybe this will help... My results look like this:

(id) & (cd) & (date)

12003 & 451 & 2006-05-01 12:35:00
12005 & 358 & 2006-05-02 12:45:00
12005 & 348 & 2006-05-03 14:25:00
12006 & 10 & 2006-05-03 22:30:00
12003 & 350 & 2006-05-03 9:35:00
12005 & 125 & 2006-05-04 11:05:00

I need my results to end up like this:

12006 & 10 & 2006-05-03 22:30:00
12003 & 350 & 2006-05-03 9:35:00
12005 & 125 & 2006-05-04 11:05:00

Make sense? Any new ideas?

[.josh]

how about:

SELECT id, cb FROM $table_name ORDER BY date DESC LIMIT 1

[rburch]

Nope. That will only give you this from my example:

12005 & 125 & 2006-05-04 11:05:00

I need the latest result for each unique id.

Anyone else?