Re: SQL Brain Teaser

Maybe I've misunderstood you but the solution seems to be fairly simple. The SQL statement below would return your wanted answer as its first row:

select project_code, COUNT(DISTINCT expenditure_code) from project_costs
group by project_code
order by 2 desc

You can also create a view for the above query (without the ORDER BY clause, of course) and select MAX from its second column.

Best regards

bainbridge_i_at_perc03_at_bgers.co.uk (Ian Bainbridge) wrote:
>
> 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.
>
> 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 ?
>
Received on Thu Sep 21 1995 - 00:00:00 CEST