Обсуждение: locked my keys in the car
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
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
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
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