The PARTITION BY clause is used to reduce the window to a particular group within the dataset.įor example, given the following data: CREATE TABLE student ( name CHAR ( 10 ), test CHAR ( 10 ), score TINYINT ) INSERT INTO student VALUES ( 'Chun', 'SQL', 75 ), ( 'Chun', 'Tuning', 73 ), ( 'Esben', 'SQL', 43 ), ( 'Esben', 'Tuning', 31 ), ( 'Kaolin', 'SQL', 56 ), ( 'Kaolin', 'Tuning', 88 ), ( 'Tatiana', 'SQL', 87 ), ( 'Tatiana', 'Tuning', 83 ) By default, the set of rows used for the calculation (the 'window) is the entire dataset, which can be ordered with the ORDER BY clause. Window function queries are characterised by the OVER keyword, following which the set of rows used for the calculation is specified. However, unlike aggregate functions, the output is not grouped into a single row.Īggregate functions that can also be used as window functions include

In some ways, window functions are similar to aggregate functions in that they perform calculations across a set of rows. Now you're left with deciding which one is the best fit for your needs.Window functions allow calculations to be performed across a set of rows related to the current row. SummaryĪs you can see, different ranking functions have different goals. When identical rows were found ('apple'), they were given the rank '1', but the rank '2' wasn't skipped and was given to the next non-identical row in line ('grapes'). Example: select name, DENSE_RANK() OVER(ORDER BY name) from fruits The results:Īs you can see above, no ranks were skipped, all 1 to 4 are there. When using DENSE_RANK, the same rules apply as we described for RANK(), with one difference - when similar rows are detected, the next rank in line isn't skipped. Therefore, the next row ('grapes') received the rank '3'.

Also, once 'apple' was ranked with the number '1', the number '2' was skipped because the second row is identical. You can see above that identical rows (such as 'apple') were ranked with the same number. Example: select name, RANK() OVER(ORDER BY name) from fruits The results: Also, please note that if the function skipped a number while ranking (because of row similarity), that rank will be skipped. The only difference is that identical rows are marked with the same rank. This function is very similar to the ROW_NUMBER() function. You can see above that the results are ordered by the column we declared in the ORDER BY clause, and ranked accordingly. Example: select name, ROW_NUMBER() OVER(ORDER BY name) from fruits The results: This function will just rank all selected rows in an ascending order, regardless of the values that were selected.

This is the simplest of all to understand.

Let's look into the differences and similarities between three of them: RANK(), DENSE_RANK() and ROW_NUMBER().įor the sake of comparison, we'll work with the following demo table and values: PostgreSQL offers several ranking functions out of the box.

Rank function in postgresql