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.
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_Codeorder by Postcode,count(*) descand getting the following results:3161 16 52483161 03 24273161 22 9893161 01 8593161 21 2523161 24 1463161 20 1303161 17 503161 11 413161 18 383161 25 353161 19 173166 22 63473166 11 29533166 21 9683166 14 8343166 24 1603166 25 723166 12 403166 29 37What 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 thisselect * from (select Postcode, MOSAIC_Type_Code, count(*) as Total from @twhere (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))group by Postcode, MOSAIC_Type_Code) twhere Total in (Select max(Total) from( select Postcode, MOSAIC_Type_Code, count(*) as Total from @twhere (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))group by Postcode, MOSAIC_Type_Code) newt ) Madhivanan |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 @twhere (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))group by Postcode, MOSAIC_Type_Code) twhere Total =(Select max(Total) from( select Postcode, MOSAIC_Type_Code, count(*) as Total from @twhere (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))group by Postcode, MOSAIC_Type_Code) newt where t.postcode=newt.postcode) |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 @twhere (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))group by Postcode, MOSAIC_Type_Codeorder by Postcode,count(*) desc |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
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 @twhere (MOSAIC_Type_Code is not Null) and (Postcode in ('3161','3166'))group by Postcode, MOSAIC_Type_Codeorder by Postcode,count(*) desc
No way, man |
![Go to Top of Page Go to Top of Page](/forums/images/icon_go_up.gif) |
|
|
|
|
|
|