How to select a max row for each group in SQL



CREATE TABLE [dbo].[test1]([Country] [varchar](4) NULL,  [grpid] [int] NOT NULL,  [Value] [int] NULL,  [Row_num] [int] NULL) 

INSERT [dbo].[test1]  VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)
, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14)
,(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)

GO

/*
Country	grpid	Value	Row_num
US	49707	604456458	1
GB	5086	497654945	4
CA	909	353500201	10
JP	231	198291290	15

*/

--source table with a generated row number based on value and grpid
;with mytest as (
select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from test1
)

,mycte1 as (select grpid,country,value, Row_num, row_number() over(Order by value DESC, grpid DESC) rn
from mytest a where not exists(select 1  from mytest b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(  Order by value DESC, grpid DESC) rn 
 from mycte1 a where not exists(select 1  from mycte1 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )  
,mycte3 as (select grpid,country,value , Row_num, row_number() over(  Order by value DESC, grpid DESC) rn from mycte2 a 
where not exists(select 1  from mycte2 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) ) 

 --Add more
--,mycte4 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn
-- from mycte3 a where not exists(select 1  from mycte3 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) ) 
--,mycte5 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn from mycte4 a 
--where not exists(select 1  from mycte4 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
  

--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte1 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte3 WHERE rn=1


 ----Add more
--Union ALL
--select grpid,country,value , Row_num from mycte4 WHERE rn=1
--Union ALL
--select grpid,country,value , Row_num from mycte5 WHERE rn=1

 

drop table test1
 


--Early version
CREATE TABLE [dbo].[test1](
       [Country] [varchar](4) NULL,
       [grpid] [int] NOT NULL,
       [Value] [int] NULL,
       [Row_num] [int] NULL
) 

INSERT [dbo].[test1]  VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)

, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14),(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)

GO

/*
Country	grpid	Value	Row_num
US	49707	604456458	1
GB	5086	497654945	4
CA	909	353500201	10
JP	231	198291290	15

*/

;with mytest as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from test1)

,mycte1 as (select grpid,country,value , Row_num from mytest a where not exists(select 1  from mytest b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(Order by value DESC, grpid DESC) rn   from mycte1 )
,mycte3 as (select grpid,country,value , Row_num from mycte1 a where not exists(select 1  from mycte2 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte4 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from mycte3 )
,mycte5 as (select grpid,country,value , Row_num from mycte3 a where not exists(select 1  from mycte4 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte6 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from mycte5 )
,mycte7 as (select grpid,country,value , Row_num from mycte5 a where not exists(select 1  from mycte6 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte8 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn  from mycte7 ) 
,mycte9 as (select grpid,country,value , Row_num from mycte7 a where not exists(select 1  from mycte8 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
  

--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte4 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte6 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte8 WHERE rn=1

 

drop table test1


 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc8f52e2-a03d-4183-bf35-dc2ab14f2632/how-to-select-a-max-row-for-each-group-in-sql?forum=transactsql#5118d2c5-2068-417e-9578-865877eb8925

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s