Explain RANK() in PostgreSQL

✅ RANK() in PostgreSQL

The RANK() function in PostgreSQL assigns a rank to each row within a partition of a result set. It’s part of window functions.

---

✅ Basic Syntax:

SELECT column1, column2, RANK() OVER (ORDER BY columnX DESC) AS rank
FROM table_name;

 

✅ Example:

Sample Table: sales

id	name	amount
1	Alice	500
2	Bob	700
3	Carol	700
4	David	300

SELECT name, amount,
       RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

✅ Output:
name    amount    rank
Bob    700    1
Carol    700    1
Alice    500    3
David    300    4

✅ Ranks are shared → same amount → same rank → skips numbers (1, 1, 3, 4).
✅ Difference Between RANK() and DENSE_RANK()
Function    Behavior
RANK()    Skips ranks if there are ties.
DENSE_RANK()    Does not skip ranks; ranks are dense.
✅ Example with DENSE_RANK():

SELECT name, amount,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

name    amount    rank
Bob    700    1
Carol    700    1
Alice    500    2
David    300    3
✅ ✅ Summary:

    RANK() → skips rank numbers if tied.

    DENSE_RANK() → no gaps in ranking.

    You can partition by category using:

RANK() OVER (PARTITION BY department ORDER BY amount DESC)