Post reply

Top 2 records with range

  • 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

  • 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)

  • 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)

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy