pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Simulating LIMIT/OFFSET in a subquery
2+ messages / 2 participants
[nested] [flat]

* Simulating LIMIT/OFFSET in a subquery
@ 2001-02-12 02:13 Jamie Walker <jamie@sagaxis.co.uk>
  2001-02-12 04:10 ` Re: Simulating LIMIT/OFFSET in a subquery Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Jamie Walker @ 2001-02-12 02:13 UTC (permalink / raw)
  To: pgsql-sql

I need to write querys that return the second and third record from a 'visits' table, for each patient. (What I need to do is look at how many patients were diagnosed on the first/second/third visit to see a physician at the outpatient clinic).

I can get at the first visit using DISTINCT ON:

SELECT DISTINCT ON (sy.episodeid) sy.episodeid, fu.opdid 
FROM breast_tblfollowup fu, breast_sympt sy, outpatients opd 
WHERE sy.episodeid = opd.episodeid AND fu.opdid = opd.recordno 
ORDER BY sy.episodeid, dateopappt;

( breast_sympt is a view returning the subset of patients that i wish to examine, opdid is the primary key in breast_tblfollowup, which is linked one-to-one to the primary key of outpatients, called recordno )


... but I run into difficulties getting the second and third. I tried:

SELECT sy.episodeid, fu.opdid FROM breast_tblfollowup fu, breast_sympt sy 
WHERE fu.opdid = (SELECT fu2.opdid FROM breast_tblfollowup fu2, outpatients opd2 
WHERE fu2.opdid = opd2.recordno AND opd2.episodeid = sy.episodeid 
ORDER BY opd2.dateopappt LIMIT 1 OFFSET 1);

But of course, ORDER BY and LIMIT are not allowed in sub-queries.

Does anyone know how to work around this? I thought about creating a function that returns the primary key from the nth visit for a particular patient, but I am looking for a more general solution as I have a huge list of queries that all ask for similar things on different tables, and i don't want to have to create similar functions for each query.

Thanks for your help!
-- 
Jamie Walker
jamie@sagaxis.co.uk





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Simulating LIMIT/OFFSET in a subquery
  2001-02-12 02:13 Simulating LIMIT/OFFSET in a subquery Jamie Walker <jamie@sagaxis.co.uk>
@ 2001-02-12 04:10 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-02-12 04:10 UTC (permalink / raw)
  To: Jamie Walker <jamie@sagaxis.co.uk>; +Cc: pgsql-sql

"Jamie Walker" <jamie@sagaxis.co.uk> writes:
> But of course, ORDER BY and LIMIT are not allowed in sub-queries.

FWIW, they are allowed as of 7.1 ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-02-12 04:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-12 02:13 Simulating LIMIT/OFFSET in a subquery Jamie Walker <jamie@sagaxis.co.uk>
2001-02-12 04:10 ` Tom Lane <tgl@sss.pgh.pa.us>

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