One of the best features in SQL are window functions. Dimitri Fontaine put it bluntly:
There was SQL before window functions and SQL after window functions
If you’re lucky enough to be using any of these databases, then you can use window functions yourself:
- CUBRID
- DB2
- Firebird
- H2
- Informix
- MariaDB
- MySQL
- Oracle
- PostgreSQL
- SQLite
- SQL Server
- Sybase SQL Anywhere
- Teradata
One of the most obvious and useful set of window functions are ranking functions where rows from your result set are ranked according to a certain scheme. There are three ranking functions:
ROW_NUMBER()
RANK()
DENSE_RANK()
The difference is easy to remember. For the examples, let’s assume we have this table (using PostgreSQL syntax):
CREATE TABLE t(v) AS
SELECT * FROM (
VALUES('a'),('a'),('a'),('b'),
('c'),('c'),('d'),('e')
) t(v)
ROW_NUMBER()
… assigns unique numbers to each row within the PARTITION
given the ORDER BY
clause. So you’d get:
SELECT v, ROW_NUMBER() OVER()
FROM t
Note that some SQL dialects (e.g. SQL Server) require an explicit ORDER BY
clause in the OVER()
clause:
SELECT v, ROW_NUMBER() OVER(ORDER BY v)
FROM t
The above query returns:
| V | ROW_NUMBER | |---|------------| | a | 1 | | a | 2 | | a | 3 | | b | 4 | | c | 5 | | c | 6 | | d | 7 | | e | 8 |
RANK()
… behaves like ROW_NUMBER()
, except that “equal” rows are ranked the same. If we substitute RANK()
into our previous query:
SELECT v, RANK() OVER(ORDER BY v)
FROM t
… then the result we’re getting is this:
| V | RANK | |---|------| | a | 1 | | a | 1 | | a | 1 | | b | 4 | | c | 5 | | c | 5 | | d | 7 | | e | 8 |
As you can see, much like in a sports ranking, we have gaps between the different ranks. We can avoid those gaps by using
DENSE_RANK()
Trivially, DENSE_RANK()
is a rank with no gaps, i.e. it is “dense”. We can write:
SELECT v, DENSE_RANK() OVER(ORDER BY v)
FROM t
… to obtain
| V | DENSE_RANK | |---|------------| | a | 1 | | a | 1 | | a | 1 | | b | 2 | | c | 3 | | c | 3 | | d | 4 | | e | 5 |
One interesting aspect of DENSE_RANK()
is the fact that it “behaves like” ROW_NUMBER()
when we add the DISTINCT
keyword.
SELECT DISTINCT v, DENSE_RANK() OVER(ORDER BY v)
FROM t
… to obtain
| V | DENSE_RANK | |---|------------| | a | 1 | | b | 2 | | e | 5 | | d | 4 | | c | 3 |
In fact, ROW_NUMBER()
prevents you from using DISTINCT
, because ROW_NUMBER()
generates unique values across the partition before DISTINCT
is applied:
SELECT DISTINCT v, ROW_NUMBER() OVER(ORDER BY v)
FROM t
ORDER BY 1, 2
DISTINCT
has no effect:
| V | ROW_NUMBER | |---|------------| | a | 1 | | a | 2 | | a | 3 | | b | 4 | | c | 5 | | c | 6 | | d | 7 | | e | 8 |
Putting it all together
A good way to understand the three ranking functions is to see them all in action side-by-side. Run this query
SELECT
v,
ROW_NUMBER() OVER(ORDER BY v),
RANK() OVER(ORDER BY v),
DENSE_RANK() OVER(ORDER BY v)
FROM t
ORDER BY 1, 2
… or this one (using the SQL standard WINDOW
clause, to reuse window specifications):
SELECT
v,
ROW_NUMBER() OVER(w),
RANK() OVER(w),
DENSE_RANK() OVER(w)
FROM t
WINDOW w AS (ORDER BY v)
… to obtain:
| V | ROW_NUMBER | RANK | DENSE_RANK | |---|------------|------|------------| | a | 1 | 1 | 1 | | a | 2 | 1 | 1 | | a | 3 | 1 | 1 | | b | 4 | 4 | 2 | | c | 5 | 5 | 3 | | c | 6 | 5 | 3 | | d | 7 | 7 | 4 | | e | 8 | 8 | 5 |
Note that unfortunately, the WINDOW
clause is not supported in all databases.
SQL is awesome
These things can be written very easily using SQL window functions. Once you get a hang of the syntax, you won’t want to miss this killer feature in your every day SQL statements any more. Excited?

For further reading, consider:
Great explanation. Window functions have many operations and it’s easy to get lost when you start working with them. I always like to remind Java developers that Hibernate query language is no match for window functions, pivot or recursive common table expressions. It’s pretty sad to find out that the vast majority of Java developers have never heard of these SQL features.
It isn’t sad, it’s a challenge, and good grounds for SQL evangelism!
Excellent! Well explained. Really helped me.
Thanks
Glad it did, thanks for the feedback!
Nice Explanation !
Excellent way of explanation,Thanks
very well explained. Very much helpful for interview preparation.
Thanks.
How to use ROW_NUMBER or similar function in Firebird? Need to have current row number in SELECT.
Use Firebird 3.0!
http://www.firebirdsql.org/file/community/conference-2014/pdf/02_fb.2014.whatsnew.30.en.pdf
How to find out the 2nd highest or 3rd highest using these functions?
You wrap them in a derived table and query on that:
Thank you so much its working…
Interesting, that works too, of course
Not sure which server this is intended for but when I run this I am getting the following error.
Incorrect syntax near ‘|’.
There’s a small remark in the middle of the article: “(using PostgreSQL syntax)”. Your error message suggests you may have executed a result set, because there is no pipe symbol in any SQL statement… :P
how to avoid duplicates here:
Rename columns using aliasing, e.g.
Hi, if I have a column of indicator, say, it is like 00100100001000001111, what I hope to do is that when the SQL sees 1, it will automatically increase the index by 1, that is to say, in this example, we will end up with 111222333334444445678, something like this.
Any idea of how to use SQL do this?
Best,
Felix
That’s an excellent question for https://stackoverflow.com
Thank you!
Very Well Explained…
Very good explanation, Thank you.
You may want to update your list of DBs with Window Functions since MariaDB now has window functions.
Thanks. Added also H2, MySQL, SQLite
Thanks
I was struggling to deal with a situation of repeated records and after seeing the example with dense_rank helped me to find my solution (dense_rank() over (partition by (…) )