Re: SQL Brain Teaser

From: <champs_at_cbr.hhcs.gov.au>
Date: 1995/09/28
Message-ID: <1995Sep28.090349.1_at_cbr.hhcs.gov.au>#1/1


In article <43pm8d$2qr_at_atlas.bgers.co.uk>, bainbridge_i_at_perc03@bgers.co.uk (Ian Bainbridge) writes:
>
> Can anybody lend a hand to this brain teaser because I seem to have
> developed brain death ?
>
> I have a table Project_costs ( project_code, expenditure_code ......)
>
> There are 1-n items per project_code,expenditure_code combination.
>
> What I am trying to determine with one piece of SQL is the number of
> different expenditure_codes per project_code and ultimately the maximum
> of those counts returned.

This should do the trick:

select project_code, count(distinct expenditure_code) from project_costs
group by project_code
having count(distinct expenditure_code) =

	(select max(count(distinct expenditure))
	 from project costs
	 group by project_code)
	 

>
> I know I could do a select distinct of project_code and then loop for
> each project_code find the number of distinct occurences of expedniture_code
> and track the maximum of those myself but I just feel it should be possible in
> one pass.
>
> Am I just off the planet today ?
>
> Any/all help/suggestions gratefully received.
> --
> +--------------------------+-------------------------+-----------------------+
> | Ian Bainbridge | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
> | British Gas ERS | Newcastle Upon Tyne, UK | x2790 |

 +--------------------------+-------------------------+-----------------------+
-- 
Bye for now.
Steven C.
--------------------------------------------------------------------------------
Steven Champness                   champs_at_cbr.hhcs.gov.au
Dept of Human Services & Health    Brisbane, Queensland, Australia
These opinions are volatile and should be stored below 4 degrees Celcius
Received on Thu Sep 28 1995 - 00:00:00 CET

Original text of this message