T-SQL 101: 91 Determining positions in a set by using RANK and DENSE_RANK

In my last T-SQL 101 post, I mentioned ROW_NUMBER. It let you put a row number or position beside each row that was returned. Sometimes though, you want a rank instead. A rank is similar but it’s like a position in a race.

In the example above, I’ve used RANK to produce an ordering, based on an alphabetical listing of city names. Notice there’s Abercorn Abercorn Abercorn and then Aberdeen. So, like in a race, if three people came first they all get the value 1. The next person is fourth. Three people came forth, so then the next one is 7th, and so on.

Dense Rank

That’s the most common form of output but occasionally, people want the same thing, but without gaps. DENSE_RANK is like RANK, but it doesn’t have gaps.

This is almost like getting to come second in a race, even though three people came first. So the idea here is that Abercorn there’s three of them, and they get the value 1. Then there’s Aberdeen. There’s three of those, so they all get the value 2. Abminga comes next, so it gets 3. And so on.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-03-22