selecting max in groups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

selecting max in groups

Author

Topic

marat
Yak Posting Veteran

85 Posts

Posted - 2005-01-12 : 22:53:50

Hi,
I am running the following query:
select Postcode, MOSAIC_Type_Code, count(*) as Total
from dbo.T_Person_Data_View
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
order by Postcode,count(*) desc
and getting the following results:

3161 16 5248
3161 03 2427
3161 22 989
3161 01 859
3161 21 252
3161 24 146
3161 20 130
3161 17 50
3161 11 41
3161 18 38
3161 25 35
3161 19 17
3166 22 6347
3166 11 2953
3166 21 968
3166 14 834
3166 24 160
3166 25 72
3166 12 40
3166 29 37
What I actually need is
only top 1 line (with max Tatals) for each postcode.
If anybody knows is it possible to do that with one sql statement.
Thank you
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-13 : 00:44:35

Try this



select * from (
select Postcode, MOSAIC_Type_Code, count(*) as Total 
from @t
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
) t
where Total in (Select max(Total) from(
 select Postcode, MOSAIC_Type_Code, count(*) as Total 
from @t
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
) newt )

Madhivanan

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-01-13 : 01:38:17

select * from (
I think
select Postcode, MOSAIC_Type_Code, count(*) as Total
from @t
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
) t
where Total =(Select max(Total) from(
select Postcode, MOSAIC_Type_Code, count(*) as Total
from @t
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
) newt where t.postcode=newt.postcode)

marat
Yak Posting Veteran

85 Posts

Posted - 2005-01-13 : 02:00:57

Hi,
I tried script by Hippy, ... it works.
Cool.
PS. I was hoping that there would be something like:
select top 1 Postcode, MOSAIC_Type_Code, count(*) as Total
from @t
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
order by Postcode,count(*) desc

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-01-13 : 11:02:02

quote:
Originally posted by marat

Hi,
I tried script by Hippy, ... it works.
Cool.
PS. I was hoping that there would be something like:
select top 1 Postcode, MOSAIC_Type_Code, count(*) as Total
from @t
where (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))
group by Postcode, MOSAIC_Type_Code
order by Postcode,count(*) desc

No way, man

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources