From: Federico Passaro <fede@link.it>
To: "Thomas Good <tomg@q8.nrnet.org, PostgreSQL sql" <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] locked my keys in the car
Date: Mon, 03 Aug 1998 16:01:01 +0200
Message-ID: <35C5C29C.ABA30D9B@link.it> (raw)
References: <Pine.SV4.3.91.980803090424.2288B-100000@q8.nrnet.org>
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
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, PostgreSQL sql" <pgsql-sql@postgreSQL.org
Subject: Re: [SQL] locked my keys in the car
In-Reply-To: <35C5C29C.ABA30D9B@link.it>
References: <Pine.SV4.3.91.980803090424.2288B-100000@q8.nrnet.org> <35C5C29C.ABA30D9B@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