/* Available in MySQL, SQL Server, PostgreSQL */
SELECT
Name,
CountryCode,
ROW_NUMBER() over p_code AS _row_number,
RANK() over p_code AS _rank,
DENSE_RANK() over p_code AS _dense_rank
FROM city
WHERE
Name = 'San Jose'
WINDOW p_code AS (PARTITION BY Name ORDER BY CountryCode)
| Name | CountryCode | _row_number | _rank | _dense_rank |
| San José | CRI | 1 | 1 | 1 |
| San Jose | PHL | 2 | 2 | 2 |
| San Jose | PHL | 3 | 2 | 2 |
| San Jose | USA | 4 | 4 | 3 |
| ROW_NUMBER() calculates the ordinal number of rows within a group regardless of whether there are duplicates in the rows. RANK() - the function calculates the rank of each row within a group. If there are duplicates, the function will return the same rank value for those rows, skipping over the next numerical rank. DENSE_RANK() is the same as RANK. If the values are equal, DENSE_RANK doesn't skip the next numerical rank, it goes sequentially instead. |