none
How to select a max row for each group in SQL RRS feed

  • Question

  • Dear Frindz,

    I want select countries with maximum value of 'Value' for a 'grpid'.  Also already selected 'Country' should not be considered for other 'grpid' while checking the maximum. ( ie Country or grpid should not be repeated in the result )

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

    Result:

     

    Country

    grpid

    Value

    Row_number

    US

    49707

    604456458

    1

    GB

    5086

    497654945

    4

    CA

    909

    353500201

    10

    JP

    231

    198291290

    15

     


    -Sajid


    • Edited by Sajid P K Tuesday, April 1, 2014 9:17 AM spelling
    Tuesday, April 1, 2014 9:09 AM

Answers

  • --I cleaned up my early solution a little bit:
    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
     
    

    Monday, April 7, 2014 3:18 PM
    Moderator

All replies

  • Sajid,

    Check this:

    ;with cte
    as
    (
    select country,grpid,value,row_number,row_number() over(partition by country order by value desc) as rn
    from test1
    )
    select country,grpid,value,row_number
    from cte
    where rn=1


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    Tuesday, April 1, 2014 9:21 AM
  • Try this :-

    with cte as (select [Country], [grpid], [Value], [Row_number], row_number() over(partition by country order by value desc,grpid desc) as row_num from test1 )
     
      select [Country], [grpid], [Value], [Row_number] from cte where row_num = 1


    • Edited by Jitesh00JJ Tuesday, April 1, 2014 9:28 AM
    Tuesday, April 1, 2014 9:27 AM
  • Hi,

    Is this result fit the DML (the data samples) that you posted? or maybe this is just part of the result?

    the Idea of posting DDL+DML+results is that we will be able to get the same result of course :-)
    If this is result fit the DML then please try to clarified your needs explaning why those records where selected 


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, April 1, 2014 10:02 AM
    Moderator
  • Hi again,

    Maybe this is hat you are looking for?
    * I have 1 record that is not in your result but it is seem like this is what u look for

    select O.* 
    from dbo.test1 O
    INNER join (
    	select MAX(value) M,grpid
    	from dbo.test1
    	group by grpid
    ) T
    ON T.grpid = O.grpid and T.M = O.value

    * This is not the best query but the esier query to understand (I think) which give the result as i understood u need. If this is the answer then we can think on making it faster in the next step.

    the result i get are:

    Country   grpid    Value        Row_number
    GB        50147    405759433    5
    US        49707    604456458    1
    GB        5086     497654945    4
    US        909      604456458    2
    US        231      604456457    3


    [Personal Site] [Blog] [Facebook]signature


    Tuesday, April 1, 2014 10:20 AM
    Moderator
  • Hi Pituach, thanks for your reply.

    in result  Country or grpid should not be repeated... in your result 'GB' and 'US' is  repeating.

      1) select countries with maximum value of 'Value' for a 'grpid'

      2) 'grpid' & country should not be repeated.

      3)  we need to take next maximum  value if 'grpid'  not  there a for  highest value.

    Result:

     

    Country

    grpid

    Value

    Row_number

    US

    49707

    604456458

    1

    GB

    5086

    497654945

    4

    CA

    909

    353500201

    10

    JP

    231

    198291290

    15

     


    -Sajid


    -Sajid


    • Edited by Sajid P K Tuesday, April 1, 2014 11:11 AM
    Tuesday, April 1, 2014 11:02 AM
  • Which version are you using? Are you on SQL 2012?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, April 1, 2014 11:31 AM
  • you can use unique keyword for that...
    Tuesday, April 1, 2014 11:45 AM
  • SQL 2008 R2

    -Sajid

    Tuesday, April 1, 2014 11:46 AM
  • Hello,

    Please refer to the following query:

    ;with cte as (
    select country, grpid, value,Row_number  from (
    select * ,
    Row_number() over (partition by grpid order by row_number asc) as rn1,
    Row_number() over (partition by country order by row_number asc ) as rn2
     from test1 ) t
     where rn1=rn2
    )
    select  country, grpid, value,Row_number  from  cte  where Row_number In (
    select  min(row_number)
    from cte
    group by grpid)
    order by Row_number
    

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support

    Wednesday, April 2, 2014 8:08 AM
    Moderator
  • Fanny,

    The query returns the result the OP asked. Good trick.

    But what is the logic of  where rn1=rn2 ?

    Also, if you change the data row a little bit, it seems the query will not work correctly to the requirement.


    Wednesday, April 2, 2014 6:59 PM
    Moderator
  • Naomi,

    The query for the early thread you mentioned can return the result requested but not returning the full result list for this data sample.

    Wednesday, April 2, 2014 8:30 PM
    Moderator
  • Fanny,

    The query returns the result the OP asked. Good trick.

    But what is the logic of  where rn1=rn2 ?

    Also, if you change the data row a little bit, it seems the query will not work correctly to the requirement.


    Hi Jingyang,

    Thanks for your reminders.  I will continue to look for better solution.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Friday, April 4, 2014 2:20 AM
    Moderator
  • Would the below help you?

    ;With cteCountry as 
    (Select *,
    MAX(Value)Over(PARTITION by Country) MaxV,
    ROW_NUMBER()Over(PARTITION by Country order by Value desc)Rn From test1 )
    ,cteGroup as 
    (Select *,
    MAX(Value)Over(PARTITION by grpid) MaxV,
    ROW_NUMBER()Over(PARTITION by grpid order by Value desc)Rn From test1 )
    ,cteIntermediate as
    (
    Select A.* From cteGroup A
     Inner join cteCountry B on A.Country = B.Country and A.grpid = B.grpid and A.Rn=B.Rn
     and A.MaxV = B.MaxV and A.Rn=1
    )
    Select A.Country,A.grpid,A.Value,A.Row_number From 
    (
    	Select * From cteIntermediate 
    	Union All
    	Select * From cteGroup A
    	where Not exists( Select 1 From cteIntermediate B where A.Country = B.Country OR A.GrpID = B.grpId)
    	and Exists(Select 1 From cteCountry B where A.Country = B.Country and A.grpid = B.grpid and A.Rn=B.Rn )
    ) A Order by ROW_NUMBER Asc
    
    

    Friday, April 4, 2014 12:02 PM
  • 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

    Friday, April 4, 2014 10:16 PM
    Moderator
  • Hi,

    1. Please close the first thread is the issue is close and you got an answer.

    2. I recommend to add more data in order to show us what is wrong in each solution. It is look like we dont get what u need (I know I am not sure now).

    Please try again clarify the needs regarding the solutions which u got


    [Personal Site] [Blog] [Facebook]signature

    Saturday, April 5, 2014 1:46 AM
    Moderator
  • --I cleaned up my early solution a little bit:
    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
     
    

    Monday, April 7, 2014 3:18 PM
    Moderator