pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlocked my keys in the car
9+ messages / 5 participants
[nested] [flat]
* locked my keys in the car
@ 1998-07-31 18:03 Thomas Good <tomg@q8.nrnet.org>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Good @ 1998-07-31 18:03 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-01 05:58 Federico Passaro <fede@link.it>
parent: Thomas Good <tomg@q8.nrnet.org>
0 siblings, 1 reply; 9+ messages in thread
From: Federico Passaro @ 1998-08-01 05:58 UTC (permalink / raw)
To: Thomas Good <tomg@q8.nrnet.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-03 13:10 Thomas Good <tomg@q8.nrnet.org>
parent: Federico Passaro <fede@link.it>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Good @ 1998-08-03 13:10 UTC (permalink / raw)
To: pgsql-sql; +Cc: UserGuideDog <ugd@geeky1.ebtech.net>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-03 14:01 Federico Passaro <fede@link.it>
parent: Thomas Good <tomg@q8.nrnet.org>
0 siblings, 0 replies; 9+ messages in thread
From: Federico Passaro @ 1998-08-03 14:01 UTC (permalink / raw)
To: "Thomas Good <tomg@q8.nrnet.org, PostgreSQL sql" <pgsql-sql@postgreSQL.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-03 17:43 Phil R Lawrence <prl2@lehigh.edu>
0 siblings, 0 replies; 9+ messages in thread
From: Phil R Lawrence @ 1998-08-03 17:43 UTC (permalink / raw)
To: ugd@geeky1.ebtech.net; pgsql-sql; +Cc: UserGuideDog <ugd@geeky1.ebtech.net>
>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!)
Try MINUS.
SQL1
MINUS
SQL2
yields SQL1's results minus any also found in SQL2.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-03 18:54 John Edstrom <edstrom@Poopsie.hmsc.orst.edu>
0 siblings, 0 replies; 9+ messages in thread
From: John Edstrom @ 1998-08-03 18:54 UTC (permalink / raw)
To: pgsql-sql
>
> And so I tried this here:
>
> SELECT tr_id, tr_date FROM crtrd1
> WHERE tr_unit = 'SMA' AND (tr_type = 'A' OR tr_type = 'I')
> AND tr_date > tr_date IN
> (SELECT tr_date FROM crtrd1
> WHERE tr_unit = 'SMA' AND (tr_type = 'T' OR tr_type = 'O'))
> ORDER BY tr_date DESC;
>
How about
CREATE FUNCTION F(int,char) returns int4 as
'SELECT max(tr_date) FROM crtd1
WHERE $1=tr_id
AND $2=tr_unit
AND (tr_type = \'A\' OR
tr_type = \'I\');'
language 'sql';
and then search as:
SELECT distinct tr_id,F(tr_id) from crtd1;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-03 20:11 Richard Lynch <lynch@lscorp.com>
0 siblings, 1 reply; 9+ messages in thread
From: lynch@lscorp.com @ 1998-08-03 20:11 UTC (permalink / raw)
To: Thomas Good <tomg@q8.nrnet.org>; pgsql-sql; +Cc: UserGuideDog <ugd@geeky1.ebtech.net>
At 9:10 AM 8/3/98, Thomas Good wrote:
>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!)
Well I'm confused. Nothing new there, eh?
If all you want is active cases, why not:
select id from table where office = 'M' and case = 'A' order by date desc;
>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?
You should be able to just mush all the stuff together in something like this
select distinct table.id from table, crtrd1 as entry, crtrd1 as exit where
table.office = 'M' and (table.case = 'A' or table.case = 'I') and
entry.tr_id = exit.tr_id and
entry.tr_unit = 'SMA' and
exit.tr_type != 'T' and exit..tr_type != 'O' and
exit.tr_date > entry.tr_date and
(entry.tr_type = 'A' or entry.tr_type = 'I');
I *THINK* this is kinda what you have asked for, but I don't really
understand for sure what your tables are, nor what you want to get out of
them...
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-03 20:41 Thomas Good <tomg@q8.nrnet.org>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas Good @ 1998-08-03 20:41 UTC (permalink / raw)
To: John Edstrom <edstrom@Poopsie.hmsc.orst.edu>; +Cc: pgsql-sql
> From: John Edstrom <edstrom@Poopsie.hmsc.orst.edu>
> To: pgsql-sql@postgreSQL.org
> How about
> CREATE FUNCTION F(int,char) returns int4 as
> 'SELECT max(tr_date) FROM crtd1
> WHERE $1=tr_id
> AND $2=tr_unit
> AND (tr_type = \'A\' OR
> tr_type = \'I\');'
> language 'sql';
>
> SELECT distinct tr_id,F(tr_id) from crtd1;
Good idea but...Arrrgh!
This is what I get back:
ERROR: There is no operator '=' for types 'int4' and 'bpchar'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR
(thanks- I will keep fiddling...)
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] locked my keys in the car
@ 1998-08-04 19:15 Thomas Good <tomg@q8.nrnet.org>
parent: Richard Lynch <lynch@lscorp.com>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas Good @ 1998-08-04 19:15 UTC (permalink / raw)
To: Richard Lynch <lynch@lscorp.com>; +Cc: pgsql-sql
Rich, Dave, Federico ---
Guys, for the assist! The last piece of the puzzle follows.
A buddy of mine in Pennsylvania was able to force the date comparison.
Rich, this is pretty close to your code, a couple of minor diffs
do the job!
SELECT tr_id, tr_date
FROM crtrd1 ALIAS1
WHERE ALIAS1.tr_unit = 'SMA'
AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I')
AND NOT EXISTS(
SELECT tr_id
FROM crtrd1 ALIAS2
WHERE ALIAS2.tr_unit = 'SMA'
AND ALIAS2.tr_id = ALIAS1.tr_id
AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O')
AND ALIAS2.tr_date > ALIAS1.tr_date
)
ORDER BY tr_date DESC;
Thanks for your assistance boys - I'm thinking of getting this
tattooed somewhere - somewhere I can see it when I need it that is!
---------- 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~1998-08-04 19:15 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-31 18:03 locked my keys in the car Thomas Good <tomg@q8.nrnet.org>
1998-08-01 05:58 ` Federico Passaro <fede@link.it>
1998-08-03 13:10 ` Thomas Good <tomg@q8.nrnet.org>
1998-08-03 14:01 ` Federico Passaro <fede@link.it>
1998-08-03 17:43 Re: [SQL] locked my keys in the car Phil R Lawrence <prl2@lehigh.edu>
1998-08-03 18:54 Re: [SQL] locked my keys in the car John Edstrom <edstrom@Poopsie.hmsc.orst.edu>
1998-08-03 20:11 Re: [SQL] locked my keys in the car Richard Lynch <lynch@lscorp.com>
1998-08-04 19:15 ` Thomas Good <tomg@q8.nrnet.org>
1998-08-03 20:41 Re: [SQL] locked my keys in the car Thomas Good <tomg@q8.nrnet.org>
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