Post reply

The Lost Last Value

  • August 8, 2018 at 11:37 pm

    #77515

    Comments posted to this topic are about the item The Lost Last Value

  • August 9, 2018 at 12:05 am

    #2000948

    Sooo, how you get what you want? Use LAG?

  • August 9, 2018 at 4:17 am

    #2000970

    You can calc the min value, like :
    MIN(points) OVER (Order by points) 

    modify the default window like:
    LAST_VALUE(points) OVER(ORDER BY points DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

    or use the FIRST_VALUE function reverting the order by
    FIRST_VALUE(s.points) OVER (ORDER BY points )

    as an option for LAG, you can numerate the rows and apply that as the offset for LAG function, like:
    LAG(points,d-1) OVER (order by points)     
    FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY POINTS) d FROM dbo.Scorers ) AS s

    same for LEAD reverting the order by
    LEAD(points,d-1) OVER (order by points DESC)     
    FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY POINTS) d FROM dbo.Scorers ) AS s

    And, just for fun, you can calc this also with recursivity.
    ;with cte as (
        select 1 as rec,playername, points, points minpoints
        from scorers
        union all
        select rec+1,c.playername , c.points , case when s.points<minpoints then s.points else minpoints end
        from cte c
            inner join scorers s
                on c.playername<>s.playername
                and s.points<c.minpoints)

    select playername,points,    
        points-minpoints from (
        select row_number() over (partition by playername order by rec desc) num,* from cte
    ) s
    where num=1
    order by points

  • August 9, 2018 at 8:52 am

    #2001043

    I've clarified the question, but really I there could be two answers. I could want this (which I wanted):

    Player     CurrentValue Behind
    Kareem Abdul-Jabbar 38387   0
    Karl Malone   36928   1459
    Kobe Bryant   33643   4744
    Michael Jordan  32292   6095
    Wilt Chamberlain  31419   6968
    DIRK NOWITZKI   31187   7200
    LeBRON JAMES   31038   7349
    Shaquille O'Neal  28596   9791

    Or this, with a running total
    Player    CurrentValue    Behind
    Kareem Abdul-Jabbar    38387    0
    Karl Malone    36928    1459
    Kobe Bryant    33643    3285
    Michael Jordan    32292    1351
    Wilt Chamberlain    31419    873
    DIRK NOWITZKI    31187    232
    LeBRON JAMES    31038    149
    Shaquille O'Neal    28596    2442

    I should have specified the choice, which is in the edited question.

    For the former, I can easily get this by specifying the window:
    SELECT 'Player'   = s.playername ,
       'CurrentValue' = s.points ,
       'Behind'   = LAST_VALUE(s.points) OVER (ORDER BY points ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - s.points
    FROM dbo.Scorers AS s
    ORDER BY s.points desc;

    For the latter, LAG() works well
    SELECT 'Player'   = s.playername ,
       'CurrentValue' = s.points ,
       'Behind'   = LAG(s.points,1, 38387) OVER (ORDER BY points desc) - s.points
    FROM dbo.Scorers AS s
    ORDER BY s.points desc;

  • August 9, 2018 at 9:28 am

    #2001061

    Just another way around the barn.
    -- How far the Player is Behind the Player ahead of them.
    WITH Rank_Scores_CTE AS
    (
    SELECT s.playername AS Player,
        s.points AS CurrentValue,
        RANK() OVER (ORDER BY s.points DESC) AS Rank_points
    FROM #Scorers AS S
    )
    SELECT l.Player, l.CurrentValue,
        L.CurrentValue - COALESCE(CTE.CurrentValue, L.CurrentValue) AS Behind
    FROM Rank_Scores_CTE AS L
        LEFT JOIN Rank_Scores_CTE AS CTE
            ON (L.Rank_points - 1) = CTE.Rank_points
    ORDER BY L.CurrentValue DESC
    ;

    -- How far the Player is behind the overall leader.
    SELECT s.playername AS Player,
        s.points AS CurrentValue,
        s.points - (SELECT MAX(points) FROM #Scorers) AS Behind
    FROM #Scorers AS S
    ORDER BY s.points DESC
    ;

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • August 10, 2018 at 2:03 am

    #2001168

    like this discussion thread
    thanks for the question, Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • August 11, 2018 at 3:54 am

    #2001347

    Just one thought Steve, you really should ALWAYS schema qualify the CREATE TABLE statement, if only to show a good example 😉
    😎

  • August 13, 2018 at 8:40 am

    #2001489

    Eirikur Eiriksson - Saturday, August 11, 2018 3:54 AM

    Just one thought Steve, you really should ALWAYS schema qualify the CREATE TABLE statement, if only to show a good example 😉
    😎

    You are right and that's fixed.

  • August 13, 2018 at 9:14 am

    #2001499

    Steve Jones - SSC Editor - Monday, August 13, 2018 8:40 AM

    Eirikur Eiriksson - Saturday, August 11, 2018 3:54 AM

    Just one thought Steve, you really should ALWAYS schema qualify the CREATE TABLE statement, if only to show a good example 😉
    😎

    You are right and that's fixed.

    And of course, everyone has a "dbp" schema in every database 😀
    😎

  • December 19, 2018 at 1:39 pm

    #2016880

    I resolved the answer by using...

    SELECT 'Player' = s.playername, 'CurrentValue' = s.points ,
       'Points Behind Leader' = FIRST_VALUE(s.points) OVER (ORDER BY points DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - s.points
    FROM dbo.Scorers AS s
    ORDER BY s.points DESC;

    Also, LBJ is currently #5 all-time point leader 😛

Viewing 10 posts - 1 through 9 (of 9 total)

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