Re: Quota query with decent performance? - Mailing list pgsql-sql

On Tue, 11 Nov 2003 18:49:31 -0500, Chester Kustarz wrote:

[... discussion of top-n query (where n=3) ...]

> select *
> from person
> where age <=
> (select age from person order by age limit 1 offset 2);

It fails when the cardinality of person is less than 3 (returns empty
set). My solution is this, which is not as beautiful any more:

SELECT *
FROM person
WHERE age <= COALESCE ( (   SELECT age FROM person   ORDER BY age ASC   LIMIT 1 OFFSET 2       -- 2=n-1 ),(   SELECT
ageFROM person   ORDER BY age DESC      -- note: opposite of ASC   LIMIT 1 )
 
);

-- 
Greetings from Troels Arvin, Copenhagen, Denmark