locked my keys in the car - Mailing list pgsql-sql

From Thomas Good
Subject locked my keys in the car
Date
Msg-id Pine.SV4.3.91.980731121551.17178A-100000@q8.nrnet.org
Whole thread Raw
List pgsql-sql
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




pgsql-sql by date:

Previous
From: Leslie Mikesell
Date:
Subject: Re: [SQL] sample database file
Next
From: lynch@lscorp.com (Richard Lynch)
Date:
Subject: Re: [SQL] What do I need to escape in an Insert ?