Postgres Pro
Downloads
Home   >   mailing lists

Thread: locked my keys in the car

locked my keys in the car

From
Thomas Good
Date:
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

Re: [SQL] locked my keys in the car

From
Federico Passaro
Date:
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

Re: [SQL] locked my keys in the car

From
Thomas Good
Date:
Hello again...

The original post in this thread was - - -

> > What I would like to do is this:
> > SELECT id, MAX(date)
> > FROM table
> > WHERE office='M'
> > AND case='A' OR case='I';

This because, my original (badly formed) query:
SELECT id, date
FROM table
WHERE office='M'
AND case='A' OR case='I';
was returning 48,552 rows...abit more data than I needed...

Thanks to Dave Madden, James Boorn, & Federico Passaro, I reduced this
output to 12,978 rows:

SELECT id, MAX(date)
FROM table
WHERE office='M'
AND case='A' OR case='I'
GROUP BY id;
But this was still problematic, as the rows are patients and
12,978 patients (in a 15 bed ward) is a bit of overcrowding!
(Although we could use the revenue... ;-)

Next I tried Richard Lynch's suggestion:
SELECT id FROM table
WHERE office = 'M' AND (case = 'A' OR case = 'I')
ORDER BY date DESC;

And this is definitely on the right track as I am now down to
75 patients (thanks Rich).

The 13 active cases (in what we loosely term reality) are amongst
the 75 returned tuples.  Moreover, I can catch the 62 inactive cases
listed amongst the output from Rich's query with:

SELECT tr_id, tr_date FROM crtrd1
WHERE tr_unit = 'SMA' AND (tr_type = 'T' OR tr_type = 'O')
ORDER BY tr_date DESC;

(In this instance, T=terminated and O=outgoing...)

So my next question is:
Is there a way, using SQL, to do the math on this, i.e., to rm the
patients who appear twice - once in the first query, once in the second?
(God forbid we re-admit the same patient at some future date!)

In other words, can I somehow go about rm'ing those patients who have
a tr_type of T or O - with a tr_date that is > the tr_date of the entry
with a tr_type of I or A?

We are getting into Byzantine complexities here, SQL wise, and I am
tempted to just dump the output to a (perl) filehandle and let perl
sort the data - but I am curious if SQL can hack it...

Thanks alot,
Tom

    ---------- 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

Re: [SQL] locked my keys in the car

From
Federico Passaro
Date:
Thomas Good wrote:

> Hello again...
>
> The original post in this thread was - - -
>
> > > What I would like to do is this:
> > > SELECT id, MAX(date)
> > > FROM table
> > > WHERE office='M'
> > > AND case='A' OR case='I';
>
> This because, my original (badly formed) query:
> SELECT id, date
> FROM table
> WHERE office='M'
> AND case='A' OR case='I';
> was returning 48,552 rows...abit more data than I needed...
>
> Thanks to Dave Madden, James Boorn, & Federico Passaro, I reduced this
> output to 12,978 rows:
>
> SELECT id, MAX(date)
> FROM table
> WHERE office='M'
> AND case='A' OR case='I'
> GROUP BY id;
> But this was still problematic, as the rows are patients and
> 12,978 patients (in a 15 bed ward) is a bit of overcrowding!
> (Although we could use the revenue... ;-)
>
> Next I tried Richard Lynch's suggestion:
> SELECT id FROM table
> WHERE office = 'M' AND (case = 'A' OR case = 'I')
> ORDER BY date DESC;
>
> And this is definitely on the right track as I am now down to
> 75 patients (thanks Rich).
>
> The 13 active cases (in what we loosely term reality) are amongst
> the 75 returned tuples.  Moreover, I can catch the 62 inactive cases
> listed amongst the output from Rich's query with:
>
> SELECT tr_id, tr_date FROM crtrd1
> WHERE tr_unit = 'SMA' AND (tr_type = 'T' OR tr_type = 'O')
> ORDER BY tr_date DESC;
>
> (In this instance, T=terminated and O=outgoing...)
>
> So my next question is:
> Is there a way, using SQL, to do the math on this, i.e., to rm the
> patients who appear twice - once in the first query, once in the second?
> (God forbid we re-admit the same patient at some future date!)
>
> In other words, can I somehow go about rm'ing those patients who have
> a tr_type of T or O - with a tr_date that is > the tr_date of the entry
> with a tr_type of I or A?
>
> We are getting into Byzantine complexities here, SQL wise, and I am
> tempted to just dump the output to a (perl) filehandle and let perl
> sort the data - but I am curious if SQL can hack it...
>
> Thanks alot,
> Tom
>
>     ---------- 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

this should be another step toward the solution of your sql problem (I
hope!)

SELECT C1.tr_id, C2.tr_date FROM crtrd1 C1, OUTER  crtrd1 C2
WHERE C1.tr_unit = 'SMA' AND (C1.tr_type = 'T' OR C1.tr_type = 'O') AND
C2.tr_unit = 'SMA' AND (C2.tr_type = 'I' OR C2.tr_type = 'A') AND
C1.tr_id = C2.tr_id AND
C1.tr_date > C2.tr_date
ORDER BY tr_date DESC;

Cheers

federico