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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
