August 8, 2018 at 11:37 pm
Comments posted to this topic are about the item The Lost Last Value
August 9, 2018 at 12:05 am
Sooo, how you get what you want? Use LAG?
August 9, 2018 at 4:17 am
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
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 totalPlayer 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 wellSELECT '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
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
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
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
Eirikur Eiriksson - Saturday, August 11, 2018 3:54 AMJust 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
Steve Jones - SSC Editor - Monday, August 13, 2018 8:40 AMEirikur Eiriksson - Saturday, August 11, 2018 3:54 AMJust 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
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