March 24, 2016 at 7:20 am
Afternoon All,
i am stumped on a lag function which i need some advice/guidance on how i could solve this issue at hand.
A data sample:
Create Table Adhoc.test
(
ID int identity(1,1) Not null,
Score int,
Rank_pos int
)
Insert into Adhoc.test
(Rank_pos, score)
Values
(1 ,100),
(NULL, 98),
(NULL, 94),
(NULL,88),
(2, 72),
(NULL, 70 ),
(NULL, 68),
(3, 43),
(NULL, 34),
(NULL, 22)
So in a nutshell the requirements are that the NULL values should show the next increment in the table. e.g. scores 88-98 should have rank pos = 2; 68 to 70 should have rank pos =3, 22-34 should have a rank pos = 4.
I have been attempting to working with a lag function, however the number of NULL values can range from 1 to 50+, so either i can use a cursor to iterate through the table or alternatively and preferably use a dynamic lag function.
I'm working my way towards my first SQL exam (70-461) so would appreciate guidance and explanations on what would be the most ideal solution.
Many Thanks
March 24, 2016 at 7:35 am
Try the awesome CASE keyword:
CASE WHEN rankord IS NULL THEN LAG(., -1) ELSE rankord END and group on that to get the actual sequence of ranking? No idea if this is valid or not but it sure makes logical sense at first blush.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 24, 2016 at 7:54 am
Hi Kevin,
Thanks for your response.
The case statement has been my savior in such situations, however when applying the lag function in this instance only yields the next null result in the table.
I think in order to use this process i would have to implement a possible infinite amount of nested statements to populate all null values, in which case i would lean towards using a cursor. I'm trying my hardest to avoid this.
The case statement i have written is:
CASE
WHEN rank_pos IS NULL
THEN Lag(Rank_pos,1,0) over (partition by NULL order by score desc)+1
ELSE rank_pos
END RP
March 24, 2016 at 7:59 am
I'm not sure that LAG function would work here.
I have 2 options that work for this example, but you might have to do further testing.
SELECT at.ID,
at.Score,
at.Rank_pos,
x.Rank_pos
FROM Adhoc_test at
CROSS APPLY ( SELECT TOP 1 i.Rank_pos
FROM Adhoc_test i
WHERE i.Score >= at.Score
AND i.Rank_pos IS NOT NULL
ORDER BY Score) x
SELECT at.ID,
at.Score,
at.Rank_pos,
ISNULL( at.Rank_pos, ID - ROW_NUMBER() OVER( PARTITION BY Rank_pos ORDER BY Score DESC)) Rank_Pos,
ISNULL( at.Rank_pos, ROW_NUMBER() OVER( ORDER BY Score DESC) - ROW_NUMBER() OVER( PARTITION BY Rank_pos ORDER BY Score DESC)) Rank_Pos
FROM Adhoc_test at
ORDER BY ID;
Another option is to use the Quirky Update, but that requires certain setup from your tables. Read more about it in here: http://qa.sqlservercentral.com/articles/T-SQL/68467/
March 24, 2016 at 9:37 am
Provided that the rank_pos is sequential and there are no gaps you use LEAD like this:
SELECT t.Score, Rank_pos = ISNULL(t.Rank_pos,X.Rank_pos+1)
FROM
(
SELECT score, Rank_pos, xx = LEAD(Score,1,1) OVER (ORDER BY Rank_pos)
FROM test
WHERE Rank_pos IS NOT NULL
UNION ALL
SELECT MIN(score), MAX(Rank_pos)+1, NULL
FROM test
) X
JOIN test t ON t.Score > X.xx AND t.Score <= X.Score;
The LAG version would look like this:
SELECT t.Score, Rank_pos = ISNULL(t.Rank_pos,X.Rank_pos+1)
FROM
(
SELECT score, Rank_pos, xx = LAG(Score,1,1) OVER (ORDER BY Rank_pos DESC)
FROM test
WHERE Rank_pos IS NOT NULL
UNION ALL
SELECT MIN(score), MAX(Rank_pos)+1, NULL
FROM test
) X
JOIN test t ON t.Score > X.xx AND t.Score <= X.Score;
-- Itzik Ben-Gan 2001
March 25, 2016 at 12:32 am
This ?
CREATE TABLE test
(
id INT IDENTITY(1, 1) NOT NULL,
score INT,
rank_pos INT
)
INSERT INTO test(rank_pos,score)
VALUES
(1 ,100),
(NULL, 98),
(NULL, 94),
(NULL,88),
(2, 72),
(NULL, 70 ),
(NULL, 68),
(3, 43),
(NULL, 34),
(NULL, 22)
SELECT id,
score,
Dense_rank() OVER(ORDER BY cnt) Rank_pos
FROM (SELECT *,
SUM(rank_pos) OVER(PARTITION BY NULL ORDER BY id)cnt
FROM test)T1
ORDER BY id
DROP TABLE test
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply