Re: Is there a more elegant way to write this query?... - Mailing list pgsql-sql

Hi Eric-

Thanks for your suggestion.

> An explain analyze would help.

I'll do that (and move the question to the performance list) when I get to
the performance question, but at this point, I'm just seeking some help in
looking at this from a different angle- I couldn't figure out how to achieve
the result I was after without using max(), which felt like a kludge. Your
solution gave me the start I needed.

-Nick


> 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;