From: "Hunter, Ray" <rhunter@enterasys.com>
> My problem is that I want to pull to specific rows from a query result.
>
> First here is the query:
> SQL-query:
> select card, status, time_stamp, comp_date
> from test_record
> where id = 45
> order by card, comp_date
>
> What I want is the two rows that are bold. However this list will
continue
> to grow and have more card types. I always want the last card type in the
> card group, because this has the comp_date that I am looking for.
I'm assuming here that id,card,time_stamp can't have duplicates and that you
want the most recent time_stamp for a specific id,card.
SELECT id,card,status,time_stamp,comp_date FROM cards c1
WHERE c1.id=45
AND c1.time_stamp =(SELECT max(time_stamp) FROM cards c2 WHERE c2.id=c1.id AND c2.card=c1.card);
What we're doing here is only selecting records where the current time_stamp
matches the maximum time_stamp for a specific id/card. If you have duplicate
time_stamp values for a specific id/card this won't work.
If this is too slow, use a temporary table to assemble
id,card,max(time_stamp) and join to the temporary table.
HTH
- Richard Huxton