pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (2+ messages)

Message-ID: <005701c0f4c9$f36d3c00$1001a8c0@archonet.com>
Permalink:  ../005701c0f4c9$f36d3c00$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/005701c0f4c9$f36d3c00$1001a8c0@archonet.com

 · 

reply

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