LIMIT within UNION?

From: Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: LIMIT within UNION?
Date: 2002-09-12 19:11:45
Message-ID: Pine.LNX.4.21.0209121454540.32000-100000@perrin.socsci.unc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Greetings-

I have a table of participants to be contacted for a study. Some are in
the "exposure" group, others in the "control" group. This is designated by
a column, typenr, that contains 1 for exposure, 2 for control.

The complication is this: I need to select 200 total. The 200 number
should include *all* those eligible in the exposure group, plus enough
from the control group to bring the total number up to 200. (Yes, there is
a valid reason for this.) Furthermore, I need to sort the output of the
two groups *together* by zip code.

What I've done is to write a script that counts the number of eligible
exposure candidates:

SELECT count(*) FROM participants WHERE <eligibility>
AND typenr=1

Then subtract that number (currently 28) from 200 to get 172 control
participants. Then the problem starts.

SELECT ... FROM participants
WHERE typenr=1 AND <eligibility>
UNION
SELECT ... FROM participants
WHERE typenr=2 LIMIT 172
ORDER BY zip;

returns ERROR: parser: parse error at or near "ORDER"

I've tried a variety of parentheses to no avail.

Can someone shed some light?

Thanks!

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists(at)perrin(dot)socsci(dot)unc(dot)edu * andrew_perrin (at) unc.edu

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jeff Hoffmann 2002-09-12 19:19:01 Re: [SQL] Latitude / Longitude
Previous Message Tom Lane 2002-09-12 18:57:07 Re: Performance inside and outside view ( WAS Re: Select the