Re: Grouping and aggregates

On Tue, 4 Jun 2002, Peter Darley wrote:

> Friends,
> I've got the following query, which doesn't work because you apparently
> can't group by table.*. I was wondering if there was any way to write this
> without having to have every field listed in the GROUP BY?
>
> My query:
> SELECT code_list.* FROM code_list LEFT JOIN codes ON
> code_list.id=codes.codeid GROUP BY code_list.* ORDER BY Count(codes.id);

Would this work?

SELECT *
FROM code_list
LEFT JOIN ( SELECT codeid,
count(*) AS codecount
FROM codes
GROUP BY codeid )
AS codes
ON code_list.id = codes.codeid
ORDER BY codecount;

--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant