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 D0E51D1B584 for ; Mon, 17 Nov 2003 22:56:24 +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 71036-01 for ; Mon, 17 Nov 2003 18:55:56 -0400 (AST) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id 9D3A0D1B52D for ; Mon, 17 Nov 2003 18:55:53 -0400 (AST) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1ALsHj-0001Yc-00 for ; Mon, 17 Nov 2003 23:55:55 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org Received: from sea.gmane.org ([80.91.224.252]) by main.gmane.org with esmtp (Exim 3.35 #1 (Debian)) id 1ALsHi-0001YU-00 for ; Mon, 17 Nov 2003 23:55:54 +0100 Received: from news by sea.gmane.org with local (Exim 3.35 #1 (Debian)) id 1ALsHh-00053S-00 for ; Mon, 17 Nov 2003 23:55:53 +0100 From: Troels Arvin Subject: Re: Quota query with decent performance? Date: Mon, 17 Nov 2003 23:55:53 +0100 Lines: 29 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/206 X-Sequence-Number: 16021 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