pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eric Soroos <eric-psql@soroos.net>
To: nickf@ontko.com
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Is there a more elegant way to write this query?...
Date: Mon, 10 Nov 2003 13:37:18 -0800
Message-ID: <0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net> (raw)
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGCEAMJGAA.nickf@ontko.com>
References: <NEBBLAAHGLEEPCGOBHDGCEAMJGAA.nickf@ontko.com>


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




view thread (5+ messages)  latest in thread

Message-ID: <0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net>
Permalink:  ../0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net/
Also on:    postgresql.org/message-id/0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: eric-psql@soroos.net, nickf@ontko.com
  Subject: Re: Is there a more elegant way to write this query?...
  In-Reply-To: <0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net>
  References: <NEBBLAAHGLEEPCGOBHDGCEAMJGAA.nickf@ontko.com> <0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net>

* 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