none
Get Top 2 records with range

    질문

  • Hi, Below the test data

    Declare @Score table(IdScore int, score int, Name varchar(100))
    
    insert into @Score(IdScore,score, name)
    select 1,0, 'poor' union all
    select 2,1500 , 'Moderate' union all
    select 3,3500, 'Good' union all
    select 4,6000 , 'Very Good

    --score range: 0 - 1500 -- poor
    --score range: 1500 - 3500 -- Moderate
    --score range: 3500 - 6000 -- Good
    --score range: > 6000 -- Very good

    Declare @User table(IDUser int, score int,RecordDt date)
    insert into @User(IDUser, score, RecordDt)
    select 340,3600,'05/31/2018' union all
    select 340,3501,'05/31/2018' union all
    select 340,2375,'05/21/2018' union all
    select 340,2375,'04/25/2018' union all
    select 340,2375,'04/23/2018' union all
    select 340,2350,'04/23/2018' union all
    select 340,2325,'04/23/2018' union all
    select 340,2275,'04/23/2018' union all
    select 340,2225,'04/23/2018' union all
    select 340,2200,'04/23/2018' union all
    select 340,2175,'04/19/2018' union all
    select 340,2150,'04/19/2018' union all
    select 340,2125,'04/19/2018' union all
    select 340,2025,'04/19/2018' union all
    select 340,1925,'04/19/2018' union all
    select 340,1900,'04/19/2018' union all
    select 340,1875,'04/19/2018' union all
    select 340,1850,'04/19/2018' union all
    select 340,1825,'04/19/2018' union all
    select 340,1800,'04/19/2018' union all
    select 340,1775,'04/19/2018' union all
    select 340,1725,'04/19/2018' union all
    select 340,1675,'04/19/2018' union all
    select 340,1650,'04/19/2018' union all
    select 340,1625,'04/19/2018' union all
    select 340,1575,'04/19/2018' union all
    select 340,1525,'04/19/2018' union all
    select 340,1475,'04/19/2018' union all
    select 340,1425,'04/18/2018' union all
    select 340,1400,'04/18/2018' union all
    select 340,1375,'04/18/2018' union all
    select 340,1350,'04/18/2018' union all
    select 340,1325,'04/18/2018' union all
    select 340,1300,'04/18/2018' union all
    select 340,1275,'04/17/2018' union all
    select 340,1250,'04/17/2018' union all
    select 340,1225,'04/17/2018' union all
    select 340,1200,'04/17/2018' union all
    select 340,1175,'04/17/2018' union all
    select 340,1150,'04/17/2018' union all
    select 340,1125,'04/17/2018' union all
    select 340,1100,'04/17/2018' union all
    select 340,1075,'04/17/2018' union all
    select 340,1050,'04/17/2018' union all
    select 340,1025,'04/17/2018' union all
    select 340,1000,'04/17/2018' union all
    select 340,975,'04/17/2018' union all
    select 340,950,'04/17/2018' union all
    select 340,925,'04/17/2018' union all
    select 340,900,'04/17/2018' union all
    select 340,875,'04/17/2018' union all
    select 340,850,'04/17/2018' union all
    select 340,825,'04/17/2018' union all
    select 340,800,'04/17/2018' union all
    select 340,775,'04/17/2018' union all
    select 340,750,'04/17/2018' union all
    select 340,725,'04/17/2018' union all
    select 340,675,'04/17/2018' union all
    select 340,625,'04/17/2018' union all
    select 340,600,'04/17/2018' union all
    select 340,575,'04/17/2018' union all
    select 340,550,'04/17/2018' union all
    select 340,525,'04/17/2018' union all
    select 340,500,'04/17/2018' union all
    select 340,475,'04/17/2018' union all
    select 340,450,'04/17/2018' union all
    select 340,425,'04/17/2018' union all
    select 340,225,'04/17/2018' union all
    select 340,200,'04/17/2018' union all
    select 340,175,'04/17/2018' union all
    select 340,150,'04/17/2018' union all
    select 340,100,'04/17/2018' union all
    select 340,75,'04/17/2018' union all
    select 340,50,'04/17/2018' union all
    select 340,0,'04/17/2018' union all
    select 340, NULL, null union all
    select 340,null, null 

    Expected Result:

    select 340 as IdUser,3600 as Score, 'Good' as Name  union all
     
      select 340 as IdUser,2375 as Score, 'moderate' as Name 
    the requirement here is, i need to get the top 2 latest record based on the rage difference. on the above example, top 2 record falls in same range (3500 - 6000)which means "Good"
    so take just most recent one. then next rage(1500-3500) record is 2375 which falls in "Moderate". 

    Any sample query how to achieve this please 



    loving dotnet

    2018년 6월 14일 목요일 오전 3:24

모든 응답

  • Hi Born2Achieve,

    Please try this.

    Declare @Score table(IdScore int, score int, Name varchar(100))
    
    insert into @Score(IdScore,score, name)
    select 1,0, 'poor' union all
    select 2,1500 , 'Moderate' union all
    select 3,3500, 'Good' union all
    select 4,6000 , 'Very Good'
    
    Declare @User table(IDUser int, score int,RecordDt date)
    insert into @User(IDUser, score, RecordDt)
    select 340,3600,'05/31/2018' union all
    select 340,3501,'05/31/2018' union all
    select 340,2375,'05/21/2018' union all
    select 340,2375,'04/25/2018' union all
    select 340,2375,'04/23/2018' union all
    select 340,2350,'04/23/2018' union all
    select 340,2325,'04/23/2018' union all
    select 340,2275,'04/23/2018' union all
    select 340,2225,'04/23/2018' union all
    select 340,2200,'04/23/2018' union all
    select 340,2175,'04/19/2018' union all
    select 340,2150,'04/19/2018' union all
    select 340,2125,'04/19/2018' union all
    select 340,2025,'04/19/2018' union all
    select 340,1925,'04/19/2018' union all
    select 340,1900,'04/19/2018' union all
    select 340,1875,'04/19/2018' union all
    select 340,1850,'04/19/2018' union all
    select 340,1825,'04/19/2018' union all
    select 340,1800,'04/19/2018' union all
    select 340,1775,'04/19/2018' union all
    select 340,1725,'04/19/2018' union all
    select 340,1675,'04/19/2018' union all
    select 340,1650,'04/19/2018' union all
    select 340,1625,'04/19/2018' union all
    select 340,1575,'04/19/2018' union all
    select 340,1525,'04/19/2018' union all
    select 340,1475,'04/19/2018' union all
    select 340,1425,'04/18/2018' union all
    select 340,1400,'04/18/2018' union all
    select 340,1375,'04/18/2018' union all
    select 340,1350,'04/18/2018' union all
    select 340,1325,'04/18/2018' union all
    select 340,1300,'04/18/2018' union all
    select 340,1275,'04/17/2018' union all
    select 340,1250,'04/17/2018' union all
    select 340,1225,'04/17/2018' union all
    select 340,1200,'04/17/2018' union all
    select 340,1175,'04/17/2018' union all
    select 340,1150,'04/17/2018' union all
    select 340,1125,'04/17/2018' union all
    select 340,1100,'04/17/2018' union all
    select 340,1075,'04/17/2018' union all
    select 340,1050,'04/17/2018' union all
    select 340,1025,'04/17/2018' union all
    select 340,1000,'04/17/2018' union all
    select 340,975,'04/17/2018' union all
    select 340,950,'04/17/2018' union all
    select 340,925,'04/17/2018' union all
    select 340,900,'04/17/2018' union all
    select 340,875,'04/17/2018' union all
    select 340,850,'04/17/2018' union all
    select 340,825,'04/17/2018' union all
    select 340,800,'04/17/2018' union all
    select 340,775,'04/17/2018' union all
    select 340,750,'04/17/2018' union all
    select 340,725,'04/17/2018' union all
    select 340,675,'04/17/2018' union all
    select 340,625,'04/17/2018' union all
    select 340,600,'04/17/2018' union all
    select 340,575,'04/17/2018' union all
    select 340,550,'04/17/2018' union all
    select 340,525,'04/17/2018' union all
    select 340,500,'04/17/2018' union all
    select 340,475,'04/17/2018' union all
    select 340,450,'04/17/2018' union all
    select 340,425,'04/17/2018' union all
    select 340,225,'04/17/2018' union all
    select 340,200,'04/17/2018' union all
    select 340,175,'04/17/2018' union all
    select 340,150,'04/17/2018' union all
    select 340,100,'04/17/2018' union all
    select 340,75,'04/17/2018' union all
    select 340,50,'04/17/2018' union all
    select 340,0,'04/17/2018' union all
    select 340, NULL, null union all
    select 340,null, null 
    
    --select * from @Score
    
    --select * from @User
    
    ;with cte as 
    (
    select 
    IdScore,
    score as start_score,
    LEAD(score,1) over (order by IdScore) as end_score,
    Name
    from @Score
    ),cte_row
    as
    (
    select T.IDUser,T.score,T1.Name,T.RecordDt, ROW_NUMBER() OVER (PARTITION BY T1.Name order by T.RecordDt desc) as rn   from @User T
    CROSS APPLY (select Name from cte where (T.score>start_score and T.score<end_score) OR (T.score>start_score and end_score is NULL)) T1
    )
    select top 2 IdUser,score,Name from cte_row 
    where rn=1
    order by  RecordDt desc

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 3:54
    중재자
  • looks like this to me

    Declare @Score table(IdScore int, score int, Name varchar(100))
    
    insert into @Score(IdScore,score, name)
    select 1,0, 'poor' union all
    select 2,1500 , 'Moderate' union all
    select 3,3500, 'Good' union all
    select 4,6000 , 'Very Good'
    --score range: 0 - 1500 -- poor
    --score range: 1500 - 3500 -- Moderate
    --score range: 3500 - 6000 -- Good
    --score range: > 6000 -- Very good
    
    Declare @User table(IDUser int, score int,RecordDt date)
    insert into @User(IDUser, score, RecordDt)
    select 340,3600,'05/31/2018' union all
    select 340,3501,'05/31/2018' union all
    select 340,2375,'05/21/2018' union all
    select 340,2375,'04/25/2018' union all
    select 340,2375,'04/23/2018' union all
    select 340,2350,'04/23/2018' union all
    select 340,2325,'04/23/2018' union all
    select 340,2275,'04/23/2018' union all
    select 340,2225,'04/23/2018' union all
    select 340,2200,'04/23/2018' union all
    select 340,2175,'04/19/2018' union all
    select 340,2150,'04/19/2018' union all
    select 340,2125,'04/19/2018' union all
    select 340,2025,'04/19/2018' union all
    select 340,1925,'04/19/2018' union all
    select 340,1900,'04/19/2018' union all
    select 340,1875,'04/19/2018' union all
    select 340,1850,'04/19/2018' union all
    select 340,1825,'04/19/2018' union all
    select 340,1800,'04/19/2018' union all
    select 340,1775,'04/19/2018' union all
    select 340,1725,'04/19/2018' union all
    select 340,1675,'04/19/2018' union all
    select 340,1650,'04/19/2018' union all
    select 340,1625,'04/19/2018' union all
    select 340,1575,'04/19/2018' union all
    select 340,1525,'04/19/2018' union all
    select 340,1475,'04/19/2018' union all
    select 340,1425,'04/18/2018' union all
    select 340,1400,'04/18/2018' union all
    select 340,1375,'04/18/2018' union all
    select 340,1350,'04/18/2018' union all
    select 340,1325,'04/18/2018' union all
    select 340,1300,'04/18/2018' union all
    select 340,1275,'04/17/2018' union all
    select 340,1250,'04/17/2018' union all
    select 340,1225,'04/17/2018' union all
    select 340,1200,'04/17/2018' union all
    select 340,1175,'04/17/2018' union all
    select 340,1150,'04/17/2018' union all
    select 340,1125,'04/17/2018' union all
    select 340,1100,'04/17/2018' union all
    select 340,1075,'04/17/2018' union all
    select 340,1050,'04/17/2018' union all
    select 340,1025,'04/17/2018' union all
    select 340,1000,'04/17/2018' union all
    select 340,975,'04/17/2018' union all
    select 340,950,'04/17/2018' union all
    select 340,925,'04/17/2018' union all
    select 340,900,'04/17/2018' union all
    select 340,875,'04/17/2018' union all
    select 340,850,'04/17/2018' union all
    select 340,825,'04/17/2018' union all
    select 340,800,'04/17/2018' union all
    select 340,775,'04/17/2018' union all
    select 340,750,'04/17/2018' union all
    select 340,725,'04/17/2018' union all
    select 340,675,'04/17/2018' union all
    select 340,625,'04/17/2018' union all
    select 340,600,'04/17/2018' union all
    select 340,575,'04/17/2018' union all
    select 340,550,'04/17/2018' union all
    select 340,525,'04/17/2018' union all
    select 340,500,'04/17/2018' union all
    select 340,475,'04/17/2018' union all
    select 340,450,'04/17/2018' union all
    select 340,425,'04/17/2018' union all
    select 340,225,'04/17/2018' union all
    select 340,200,'04/17/2018' union all
    select 340,175,'04/17/2018' union all
    select 340,150,'04/17/2018' union all
    select 340,100,'04/17/2018' union all
    select 340,75,'04/17/2018' union all
    select 340,50,'04/17/2018' union all
    select 340,0,'04/17/2018' union all
    select 340, NULL, null union all
    select 340,null, null 
    
    select IDUser,score,RecordDt,name
    from
    (
    select u.*,s.name,dense_rank() over (partition by u.IDUSer ORDER BY s.score DESC) AS Rnk,ROW_NUMBER() OVER (PARTITION BY u.IDUSer,s.score ORDER BY u.score DESC) AS Seq
    from @User u
    JOIN (
    SELECT IdScore, score,name,LEAD(score,1) OVER (ORDER BY score) AS nextscore
    FROM @Score
    )s
    ON u.score BETWEEN s.score AND s.nextscore
    )r
    where seq = 1
    and Rnk <= 2
    ORDER BY Rnk
    
    
    
    /*
    Output
    -------------------------------------------
    IDUser	score	RecordDt	name
    ----------------------------------------------
    340	3600	2018-05-31	Good
    340	2375	2018-05-21	Moderate
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 14일 목요일 오전 5:54
  • Hi Will, 

    thank you for the reply and 

    i am using sqlserver 2008R2 and the function LEAD doesn't available. any suggestions please


    loving dotnet

    2018년 6월 14일 목요일 오전 5:56
  • Hi Will, 

    thank you for the reply and 

    i am using sqlserver 2008R2 and the function LEAD doesn't available. any suggestions please


    loving dotnet

    Hi Born2Achieve,

    Please try this.

    Declare @Score table(IdScore int, score int, Name varchar(100))
    
    insert into @Score(IdScore,score, name)
    select 1,0, 'poor' union all
    select 2,1500 , 'Moderate' union all
    select 3,3500, 'Good' union all
    select 4,6000 , 'Very Good'
    
    Declare @User table(IDUser int, score int,RecordDt date)
    insert into @User(IDUser, score, RecordDt)
    select 340,3600,'05/31/2018' union all
    select 340,3501,'05/31/2018' union all
    select 340,2375,'05/21/2018' union all
    select 340,2375,'04/25/2018' union all
    select 340,2375,'04/23/2018' union all
    select 340,2350,'04/23/2018' union all
    select 340,2325,'04/23/2018' union all
    select 340,2275,'04/23/2018' union all
    select 340,2225,'04/23/2018' union all
    select 340,2200,'04/23/2018' union all
    select 340,2175,'04/19/2018' union all
    select 340,2150,'04/19/2018' union all
    select 340,2125,'04/19/2018' union all
    select 340,2025,'04/19/2018' union all
    select 340,1925,'04/19/2018' union all
    select 340,1900,'04/19/2018' union all
    select 340,1875,'04/19/2018' union all
    select 340,1850,'04/19/2018' union all
    select 340,1825,'04/19/2018' union all
    select 340,1800,'04/19/2018' union all
    select 340,1775,'04/19/2018' union all
    select 340,1725,'04/19/2018' union all
    select 340,1675,'04/19/2018' union all
    select 340,1650,'04/19/2018' union all
    select 340,1625,'04/19/2018' union all
    select 340,1575,'04/19/2018' union all
    select 340,1525,'04/19/2018' union all
    select 340,1475,'04/19/2018' union all
    select 340,1425,'04/18/2018' union all
    select 340,1400,'04/18/2018' union all
    select 340,1375,'04/18/2018' union all
    select 340,1350,'04/18/2018' union all
    select 340,1325,'04/18/2018' union all
    select 340,1300,'04/18/2018' union all
    select 340,1275,'04/17/2018' union all
    select 340,1250,'04/17/2018' union all
    select 340,1225,'04/17/2018' union all
    select 340,1200,'04/17/2018' union all
    select 340,1175,'04/17/2018' union all
    select 340,1150,'04/17/2018' union all
    select 340,1125,'04/17/2018' union all
    select 340,1100,'04/17/2018' union all
    select 340,1075,'04/17/2018' union all
    select 340,1050,'04/17/2018' union all
    select 340,1025,'04/17/2018' union all
    select 340,1000,'04/17/2018' union all
    select 340,975,'04/17/2018' union all
    select 340,950,'04/17/2018' union all
    select 340,925,'04/17/2018' union all
    select 340,900,'04/17/2018' union all
    select 340,875,'04/17/2018' union all
    select 340,850,'04/17/2018' union all
    select 340,825,'04/17/2018' union all
    select 340,800,'04/17/2018' union all
    select 340,775,'04/17/2018' union all
    select 340,750,'04/17/2018' union all
    select 340,725,'04/17/2018' union all
    select 340,675,'04/17/2018' union all
    select 340,625,'04/17/2018' union all
    select 340,600,'04/17/2018' union all
    select 340,575,'04/17/2018' union all
    select 340,550,'04/17/2018' union all
    select 340,525,'04/17/2018' union all
    select 340,500,'04/17/2018' union all
    select 340,475,'04/17/2018' union all
    select 340,450,'04/17/2018' union all
    select 340,425,'04/17/2018' union all
    select 340,225,'04/17/2018' union all
    select 340,200,'04/17/2018' union all
    select 340,175,'04/17/2018' union all
    select 340,150,'04/17/2018' union all
    select 340,100,'04/17/2018' union all
    select 340,75,'04/17/2018' union all
    select 340,50,'04/17/2018' union all
    select 340,0,'04/17/2018' union all
    select 340, NULL, null union all
    select 340,null, null 
    
    --select * from @Score
    
    --select * from @User
    
    ;with cte as 
    (
    select 
    T.IdScore,
    T.score as start_score,
    T1.score as end_score,
    T.Name
    from @Score T
    LEFT JOIN @Score T1 ON T.IdScore+1=T1.IdScore --Or you could use ROW_NUMBER() function to get ids
    ),cte_row
    as
    (
    select T.IDUser,T.score,T1.Name,T.RecordDt, ROW_NUMBER() OVER (PARTITION BY T1.Name order by T.RecordDt desc) as rn   from @User T
    CROSS APPLY (select Name from cte where (T.score>start_score and T.score<end_score) OR (T.score>start_score and end_score is NULL)) T1
    )
    select top 2 IdUser,score,Name from cte_row 
    where rn=1
    order by  RecordDt desc

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 6:04
    중재자