X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6E94AD1C915 for ; Tue, 18 Nov 2003 17:01:48 +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 64500-09 for ; Tue, 18 Nov 2003 13:01:17 -0400 (AST) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id 41658D1B4E8 for ; Tue, 18 Nov 2003 13:01:16 -0400 (AST) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AM9E2-0002q6-00 for ; Tue, 18 Nov 2003 18:01:14 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org Received: from sea.gmane.org ([80.91.224.252]) by main.gmane.org with esmtp (Exim 3.35 #1 (Debian)) id 1AM9E1-0002py-00 for ; Tue, 18 Nov 2003 18:01:13 +0100 Received: from news by sea.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AM9E1-0003ID-00 for ; Tue, 18 Nov 2003 18:01:13 +0100 From: Harald Fuchs Subject: Re: Top n queries and GROUP BY Date: 18 Nov 2003 18:01:13 +0100 Organization: Linux Private Site Lines: 50 Message-ID: References: <3FB8F98D.1010707@sysd.com> Reply-To: hf99@protecting.net Mime-Version: 1.0 (generated by tm-edit 7.90) Content-Type: text/plain; charset=US-ASCII X-Complaints-To: usenet@sea.gmane.org X-No-Archive: yes X-Newsreader: Gnus v5.7/Emacs 20.7 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/144 X-Sequence-Number: 4704 In article <3FB8F98D.1010707@sysd.com>, Rich Cullingford writes: > All, > This is a straight SQL question, maybe not appropriate for a performance > list, but... > I have a simple stock holdings setup: > => select * from t1; > nam | co | num > -----+-----------+------ > joe | ibm | 600 > abe | ibm | 1500 > joe | cisco | 1200 > abe | cisco | 800 > joe | novell | 500 > joe | microsoft | 200 > What I would like to see is a Top-n-holdings-by-name", e.g, for n=2: > nam | co | num > ----------+--------+----- > joe | cisco | 1200 > joe | ibm | 600 > abe | ibm | 1500 > abe | cisco | 800 > I can get part of the way by using a LIMIT clause in a subquery, e.g, > => select 'abe', a.co, a.num from (select co, num from t1 where > nam='abe' order by num desc limit 2) as a; > ?column? | co | num > ----------+-------+------ > abe | ibm | 1500 > abe | cisco | 800 > but I can't figure out a correlated subquery (or GROUP BY arrangement or > anything else) that will cycle through the names. I vaguely remember > that these kinds or queries are hard to do in standard SQL, but I was > hoping that PG, with its extensions... How about an outer join? SELECT x1.nam, x1.co, x1.num FROM t1 x1 LEFT JOIN t1 x2 ON x2.nam = x1.nam AND x2.num > x1.num GROUP BY x1.nam, x1.co, x1.num HAVING count(*) < 2 ORDER BY x1.nam, x1.num DESC