X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 25257D1D815 for ; Tue, 18 Nov 2003 12:58:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 23554-06 for ; Tue, 18 Nov 2003 08:57:59 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id A4980D1D85E for ; Tue, 18 Nov 2003 08:57:55 -0400 (AST) Received: (qmail 31008 invoked by uid 500); 18 Nov 2003 12:56:42 -0000 Date: Tue, 18 Nov 2003 06:56:42 -0600 From: Bruno Wolff III To: Troels Arvin Cc: pgsql-sql@postgresql.org Subject: Re: Quota query with decent performance? Message-ID: <20031118125642.GC30809@wolff.to> Mail-Followup-To: Troels Arvin , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/211 X-Sequence-Number: 16026 On Mon, Nov 17, 2003 at 23:55:53 +0100, Troels Arvin wrote: > 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: Used that way the subselect value will be null if there are no matching rows. This should allow you to do something like: select * from person where not isfalse (age <= (select age from person order by age limit 1 offset 2));