Comparing Ranking Functions

Kevin Feasel

2017-10-20

T-SQL

Doug Kline compares three window functions:  RANK, DENSE_RANK, and ROW_NUMBER:

— so let’s say that we’ve created a contest

— places in the contest (top place, 2nd place, etc.)
— will be determined by the test score

— in other words, we’re not so concerned with the raw score
— but rather, we’re interested in the *relative* score
— and the order in which people appear, based on their score

— we can use the ROW_NUMBER() function to give a
— ‘ranking’ to each record, based on Score

Doug’s post is a video and an extended script so you can follow along.

Related Posts

Multiple SYSDATETIME In The Same SELECT May Give Unexpected Results

Louis Davidson walks through a scenario he experienced: The data is exactly as expected, even though the other two calls would have returned .902 and .903 if simply rounded off. On the other hand, looking for differences between the time1_3 and time2_3 columns: SELECT * FROM #hold WHERE DATEDIFF(MILLISECOND,time1_3,time2_3) <> –1; Returns 133 rows. With […]

Read More

Randomization With NEWID()

Michael J. Swart tests whether ORDER BY NEWID() produces a biased result: One of his articles, Visualizing Algorithms has some thoughts on shuffling at https://bost.ocks.org/mike/algorithms/#shuffling. He says that sorting using a random comparator is a rotten way to shuffle things. Not only is it inefficient, but the resulting shuffle is really really biased. He goes on to visualize that […]

Read More

Categories

October 2017
MTWTFSS
« Sep Nov »
 1
2345678
9101112131415
16171819202122
23242526272829
3031