From: Jamie Walker <jamie@sagaxis.co.uk>
To: pgsql-sql@postgresql.org
Subject: Simulating LIMIT/OFFSET in a subquery
Date: Sun, 11 Feb 2001 19:13:16 -0700
Message-ID: <003501c09499$bf7b2640$010a10ac@sagaxis.co.uk> (raw)
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
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: jamie@sagaxis.co.uk
Subject: Re: Simulating LIMIT/OFFSET in a subquery
In-Reply-To: <003501c09499$bf7b2640$010a10ac@sagaxis.co.uk>
References: <003501c09499$bf7b2640$010a10ac@sagaxis.co.uk>
* 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