From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | "Hunter, Ray" <rhunter(at)enterasys(dot)com>, "'Postres-sql' (E-mail)" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Getting a specific row from a table |
Date: | 2001-06-14 12:02:52 |
Message-ID: | 005701c0f4c9$f36d3c00$1001a8c0@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | DHSC Webmaster | 2001-06-14 14:49:44 | Re: performance issues |
Previous Message | Mark | 2001-06-14 11:32:20 | Moving between databases |