From: Troels Arvin <troels@arvin.dk>
To: pgsql-sql@postgresql.org
Subject: Re: Quota query with decent performance?
Date: Mon, 17 Nov 2003 23:55:53 +0100
Message-ID: <pan.2003.11.17.22.55.52.997736@arvin.dk> (raw)
References: <pan.2003.11.11.22.58.08.758402@arvin.dk>
<Pine.BSO.4.44.0311111832190.19584-100000@detroit.arbor.net>
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 age FROM person
ORDER BY age DESC -- note: opposite of ASC
LIMIT 1
)
);
--
Greetings from Troels Arvin, Copenhagen, Denmark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: troels@arvin.dk
Subject: Re: Quota query with decent performance?
In-Reply-To: <pan.2003.11.17.22.55.52.997736@arvin.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox