Re: Getting a specific row from a table - Mailing list pgsql-sql

From Richard Huxton
Subject Re: Getting a specific row from a table
Date
Msg-id 005701c0f4c9$f36d3c00$1001a8c0@archonet.com
Whole thread Raw
In response to Getting a specific row from a table  ("Hunter, Ray" <rhunter@enterasys.com>)
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Gary Stainburn
Date:
Subject: search/replace in update
Next
From: Vivek Khera
Date:
Subject: Re: Timestamp without time zone