Received: from laptop (enh38.rkymtnhi.com [204.131.47.117]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1C2Emx23816 for ; Sun, 11 Feb 2001 21:14:49 -0500 (EST) (envelope-from jamie@sagaxis.co.uk) Received: from [127.0.0.1] by laptop (ArGoSoft Mail Server, Version 1.6 (1.6.0.1)); Sun, 11 Feb 2001 19:16:02 -0700 Message-ID: <003501c09499$bf7b2640$010a10ac@sagaxis.co.uk> From: "Jamie Walker" To: Subject: Simulating LIMIT/OFFSET in a subquery Date: Sun, 11 Feb 2001 19:13:16 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f1C2Eox23817 X-Archive-Number: 200102/197 X-Sequence-Number: 1384 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