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@perrin.socsci.unc.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
>