Top 2 records with range

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  June 13, 2018 at 12:45 pm

  #355317

  Hi, Below the test daa
  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

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 14, 2018 at 11:57 am

  #1994048

  Here you go:
  CREATE TABLE #ScoreRange (
    IdScore int,
    ScoreLow int,
    ScoreHigh int,
    RangeName varchar(100)
);
INSERT INTO #ScoreRange (IdScore, ScoreLow, ScoreHigh, RangeName)
    VALUES    (1,  0, 1499, 'Poor'),
            (2, 1500, 3499, 'Moderate'),
            (3, 3500, 5999, 'Good'),
            (4, 6000, 999999999, 'Very Good');

  CREATE TABLE #Users (
    IDUser int,
    score int,
    RecordDt date
);
INSERT INTO #Users (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;

  WITH ScoreData AS (

      SELECT
        U.IDUser,
        U.score,
        SR.RangeName,
        ROW_NUMBER() OVER(PARTITION BY U.IDUser ORDER BY U.RecordDt DESC) AS RowNum
    FROM #Users AS U
        INNER JOIN #ScoreRange AS SR
            ON U.score BETWEEN SR.ScoreLow AND SR.ScoreHigh
    WHERE NOT EXISTS (
        SELECT 1
        FROM #Users AS U2
            INNER JOIN #ScoreRange AS SR2
                ON U2.score BETWEEN SR2.ScoreLow AND SR2.ScoreHigh
        WHERE U2.IDUser = U.IDUser
            AND U2.RecordDt = U.RecordDt
            AND SR2.RangeName = SR.RangeName
            AND U2.score > U.score
        )
)
SELECT SD.*
FROM ScoreData AS SD
WHERE SD.RowNum IN (1, 2)

  DROP TABLE #ScoreRange;
DROP TABLE #Users;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 14, 2018 at 12:17 pm

  #1994051

  Oops... missed a column, and while I'm at it, improved the performance characteristics by analyzing the execution plan:
  CREATE TABLE #ScoreRange (
    IdScore int,
    ScoreLow int,
    ScoreHigh int,
    RangeName varchar(100),
    UNIQUE CLUSTERED (ScoreLow ASC, ScoreHigh ASC)
);
INSERT INTO #ScoreRange (IdScore, ScoreLow, ScoreHigh, RangeName)
    VALUES    (1,  0, 1499, 'Poor'),
            (2, 1500, 3499, 'Moderate'),
            (3, 3500, 5999, 'Good'),
            (4, 6000, 999999999, 'Very Good');

  CREATE TABLE #Users (
    RowID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    IDUser int,
    score int,
    RecordDt date,
    UNIQUE CLUSTERED (RecordDt DESC, Score DESC, IDUser ASC, RowID ASC)
);
CREATE NONCLUSTERED INDEX NDX_TEMP_Users_score_INCLUDE_RecordDt_IDUser_RowID_ ON #Users
    (
    score ASC
    )
    INCLUDE (RecordDt, IDUser, RowID);
GO

  INSERT INTO #Users (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;

  WITH ScoreData AS (

      SELECT
        U.IDUser,
        U.score,
        U.RecordDt,
        SR.RangeName,
        ROW_NUMBER() OVER(PARTITION BY U.IDUser ORDER BY U.RecordDt DESC) AS RowNum
    FROM #Users AS U
        INNER JOIN #ScoreRange AS SR
            ON U.score BETWEEN SR.ScoreLow AND SR.ScoreHigh
    WHERE NOT EXISTS (
        SELECT 1
        FROM #Users AS U2
            INNER JOIN #ScoreRange AS SR2
                ON U2.score BETWEEN SR2.ScoreLow AND SR2.ScoreHigh
        WHERE U2.IDUser = U.IDUser
            AND U2.RecordDt = U.RecordDt
            AND SR2.RangeName = SR.RangeName
            AND U2.score > U.score
        )
)
SELECT SD.*
FROM ScoreData AS SD
WHERE SD.RowNum IN (1, 2)

  DROP TABLE #ScoreRange;
DROP TABLE #Users;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  June 14, 2018 at 12:20 pm

  #1994053

  
SELECT TOP (2) IDUser, Score, Name
FROM (
  SELECT U.IDUser, U.Score, U.RecordDt, S2.Name,
      ROW_NUMBER() OVER(PARTITION BY U.IDUser, S2.Name ORDER BY U.RecordDt DESC) AS row_num
  FROM @user U
  CROSS APPLY (
      SELECT TOP (1) *
      FROM @Score S
      WHERE U.score >= S.score
      ORDER BY S.score DESC
  ) AS S2
) AS query1
WHERE row_num = 1
ORDER BY RecordDt DESC

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• ranitb

  Right there with Babe

  Points: 741

  More actions

  June 20, 2018 at 5:21 am

  #1994591

  Does this help ?

  select top 2 *
from (
    select max(u.iduser) iduser, max(u.score) score, s.rangename name
    from    #Users u
            left join #scorerange s on (u.score between s.scorelow and s.scorehigh)
    group by s.rangename
) x
order by 2 desc

  O/p -
  iduser    score    name
340    3600    Good
340    2375    Moderate

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  June 20, 2018 at 9:31 am

  #1994664

  ranitb - Wednesday, June 20, 2018 5:21 AM

  Does this help ?

  select top 2 *
from (
    select max(u.iduser) iduser, max(u.score) score, s.rangename name
    from    #Users u
            left join #scorerange s on (u.score between s.scorelow and s.scorehigh)
    group by s.rangename
) x
order by 2 desc

  O/p -
  iduser    score    name
340    3600    Good
340    2375    Moderate

  Be careful with that approach: the MAX(user) may not be from the same row as the MAX(score).

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.