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 F3242D1B4E1 for ; Mon, 10 Nov 2003 21:38:12 +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 91255-03 for ; Mon, 10 Nov 2003 17:37:43 -0400 (AST) Received: from main.wiredfool.com (dsl231-055-035.sea1.dsl.speakeasy.net [216.231.55.35]) by svr1.postgresql.org (Postfix) with ESMTP id 336B2D1B527 for ; Mon, 10 Nov 2003 17:37:25 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by main.wiredfool.com (Postfix) with ESMTP id 003C02728E; Mon, 10 Nov 2003 13:32:33 -0800 (PST) Received: from main.wiredfool.com ([127.0.0.1]) by localhost (cabbage [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10875-07; Mon, 10 Nov 2003 13:32:30 -0800 (PST) Received: from [IPv6:::1] (localhost [127.0.0.1]) by main.wiredfool.com (Postfix) with ESMTP id 65F442728D; Mon, 10 Nov 2003 13:32:30 -0800 (PST) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v606) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net> Content-Transfer-Encoding: 7bit Cc: "PGSQL-SQL" From: Eric Soroos Subject: Re: Is there a more elegant way to write this query?... Date: Mon, 10 Nov 2003 13:37:18 -0800 To: X-Mailer: Apple Mail (2.606) X-Virus-Scanned: by amavisd-new-20030616-p3 (Debian) at main.wiredfool.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/100 X-Sequence-Number: 15915 On Nov 10, 2003, at 1:02 PM, Nick Fankhauser wrote: > Hi- > > I'm suffering from a performance problem, but when I look at my query, > I'm > not convinced that there isn't a better way to handle this in SQL. -So > I'm > seeking advice here before I go to the performance list. > An explain analyze would help. > What I'm trying to do is link these tables to get back a single row per > actor that shows the actor's name, the number of cases that actor is > assigned to, and if they only have one case, I want the number for that > case. This means I have to do some grouping to get the case count, but > I'm > then forced to use an aggregate function like max on the other fields. > I > hope there's a better way. Any suggestions? How about: select actor.actor_full_name, actor.actor_id, s1.ctCases, s1.case_id, case_data.case_public_id from actor inner join ( select actor_id, count(*) as ctCases, max(case_id) as case_id from actor_case_assignment group by actor_id) as s1 on (actor.actor_id = s1.actor_id) left outer join case_data using (s1.case_id=case_data.case_id) limit 1000; If you don't need the public_id, then you don't even need to join in the case data table. eric