pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Federico Passaro <fede@link.it>
To: Thomas Good <tomg@q8.nrnet.org>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] locked my keys in the car
Date: Sat, 01 Aug 1998 07:58:58 +0200
Message-ID: <35C2AEA2.56543064@link.it> (raw)
References: <Pine.SV4.3.91.980731121551.17178A-100000@q8.nrnet.org>

Thomas Good wrote:

> Hello all:
>
> I am unsuccessfully trying to convince PostgreSQL to use
> aggregates illegally as I am temporarily out_of_syntax (and
> tylenol!)
>
> What I would like to do is this:
>
> SELECT id, MAX(date)
> FROM table
> WHERE office='M'
> AND case='A' OR case='I';
>
> What the heck am I doing?  I wish I knew but what I am _trying_
> to do is this:
>
> I want to select all records that are current A(dmits) or
> I(ncoming) status - - - in M(y) office.  The problem is that
> multiple records exist and I need to get only the last record
> for *each* patient id.  I thought the last date would do it but
> I can't get SQL to tolerate my bad syntax.
> (This is a case of porting a foxpro db which is very short on
> primary keys, into SQL...the original code is more confused than
> I am...)
>
> There are multiple cases that are marked `active' for each patient,
> even in a given unit.  God knows why...
>
> Can someone translate this pseudocode into (postgre)SQL?
>
> SELECT the most recent record for each patient (based on date of entry)
> FROM accounts table
> WHERE treating_unit = 'charstr'
> AND the case is marked 'Active' or 'Incoming';
>
> Bearing in mind that the pt_id is not unique...ouch.
>
> Thanks and a free foxpro database to who ever helps me solve this!
>
>     ---------- Sisters of Charity Medical Center ----------
>                    Department of Psychiatry
>                             ----
>     Thomas Good                          <tomg@q8.nrnet.org>
>     Coordinator, North Richmond C.M.H.C. Information Systems
>     75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
>     Staten Island, NY   10304            Fax:   718-354-5056

  Hi Thomas,

I think you should tell SQL "to get only the last record for *each*
patient id.":

SELECT id, MAX(date)
FROM table
WHERE office='M'
AND case='A' OR case='I'
GROUP BY id

I hope this help, cheers

federico




view thread (9+ messages)  latest in thread

Message-ID: <35C2AEA2.56543064@link.it>
Permalink:  ../35C2AEA2.56543064@link.it/
Also on:    postgresql.org/message-id/35C2AEA2.56543064@link.it

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: fede@link.it, tomg@q8.nrnet.org
  Subject: Re: [SQL] locked my keys in the car
  In-Reply-To: <35C2AEA2.56543064@link.it>
  References: <Pine.SV4.3.91.980731121551.17178A-100000@q8.nrnet.org> <35C2AEA2.56543064@link.it>

* 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