From: Richard Huxton <dev@archonet.com>
To: Hunter, Ray <rhunter@enterasys.com>
To: 'Postres-sql' (E-mail) <pgsql-sql@postgresql.org>
Subject: Re: Getting a specific row from a table
Date: Thu, 14 Jun 2001 13:02:52 +0100
Message-ID: <005701c0f4c9$f36d3c00$1001a8c0@archonet.com> (raw)
References: <59358A738F45D51186A30008C74CE2500A079E@slc-exc1.ctron.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, rhunter@enterasys.com
Subject: Re: Getting a specific row from a table
In-Reply-To: <005701c0f4c9$f36d3c00$1001a8c0@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox