Re: Is there a more elegant way to write this query?...

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:
selectactor.actor_full_name,actor.actor_id,s1.ctCases,s1.case_id,case_data.case_public_id
fromactor 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