Re: LIMIT within UNION?

This is certainly the most elegant solution - thanks!

Andy

----------------------------------------------------------------------
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

On Thu, 12 Sep 2002, Josh Berkus wrote:

> Andrew,
>
> Another approach, one that does not require you to know before constructing
> the query how many eligible subjects there are, is not to use a union at all:
>
> SELECT * FROM (
> SELECT ... , zip
> FROM participants
> WHERE (typenr = 1 and <eligibility criteria)
> OR (typenr = 2)
> ORDER BY (typenr = 1 and <eligibility criteria>) DESC
> LIMIT 200 ) p1
> ORDER BY zip;
>
> The inner query gives you all of the records that meet the eligibility
> criteria, plus all of the records that have typenr = 2, in the order of
> whether or not they meet the criteria (as a boolean value) and truncates it
> at 200 records.
> The outer query then re-sorts this result in zip order.
>
> This seems, to me, much more flexible than using a UNION query.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>