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

Gaps And Islands: Solving Stochastic Islands Problems

Itzik Ben-Gan shares with us a special case of the islands problem: In your database you keep track of services your company supports in a table called CompanyServices, and each service normally reports about once a minute that it’s online in a table called EventLog. The following code creates these tables and populates them with […]

Read More

Ad Hoc Functions In T-SQL

Riley Major shows a couple techniques for including ad hoc functions in T-SQL, namely Common Table Expressions and the APPLY operator: It’s helpful to think of each APPLY as a pipe operation, taking the values from the previous derived table and passing them into the next to be manipulated. Programming T-SQL in this manner (loosely) approximates modern […]

Read More

Categories

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