From: Nick Fankhauser <nickf@ontko.com>
To: Eric Soroos <eric-psql@soroos.net>
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Is there a more elegant way to write this query?...
Date: Tue, 11 Nov 2003 16:23:02 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGCEFPJGAA.nickf@ontko.com> (raw)
In-Reply-To: <0F5B254A-13C6-11D8-ABDC-0003930F2A6C@soroos.net>
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;
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nickf@ontko.com, eric-psql@soroos.net
Subject: Re: Is there a more elegant way to write this query?...
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGCEFPJGAA.nickf@ontko.com>
* 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