Re: SQL Brain Teaser
Date: 1995/10/04
Message-ID: <44u9vg$nl5_at_palm.phcs.com>#1/1
bainbridge_i_at_perc03_at_bgers.co.uk (Ian Bainbridge) wrote:
> 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.
>
> 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.
A couple of people suggested
select project_code, count( distinct expenditure_code ) from PROJECT_COSTS group by project_code ;
If you add "order by 2", you'll get your counts, and the last count will be the maximum count.
For a query that returns just the maximum count, I would use
select max( count( distinct expenditure_code ) ) from PROJECT_COSTS group by project_code ;Received on Wed Oct 04 1995 - 00:00:00 CET