Re: Getting a specific row from a table

From: "Hunter, Ray" <rhunter(at)enterasys(dot)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