POSTGRESQL: Enumerate with the same number if having the same criteria

Question

What I have

id | value
1 | foo
2 | foo
3 | bah
4 | bah
5 | bah
6 | jezz
7 | jezz
8 | jezz
9 | pas
10 | log

What I need: Enumerate rows as in the following example

id | value | enumeration
1 | foo | 1
2 | foo | 1
3 | bah | 2
4 | bah | 2
5 | bah | 2
6 | jezz | 3
7 | jezz | 3
8 | jezz | 3
9 | pas | 4
10 | log | 5

I've tried row_number with over partition. But this leads to another kind of enumeration.

Thanks for any help

Answer 1

You can use rank() or dense_rank() for that case:

Click: demo:db<>fiddle

SELECT
    *,
    dense_rank() OVER (ORDER BY value)
FROM
    mytable

rank() generates an ordered number to every element of a group, but it creates gaps (if there were 3 elements in the first group, the second group starting at row 4 would get the number 4). dense_rank() avoids these gaps.

Note, this orders the table by the value column alphabetically. So, the result will be: blah == 1, foo == 2, jezz == 3, log == 4, pas == 5.

---

If you want to keep your order, you need an additional order criterion. In your case you could use the id column to create such a column, if no other is available:

Click: demo:db<>fiddle

First, use first_value() to find the lowest id per value group:

SELECT
    *,
    first_value(id) OVER (PARTITION BY value ORDER BY id)
FROM
    mytable

This first value (foo == 1, blah == 3, ...) can be used to keep the original order when calculating the dense_rank():

SELECT
    id,
    value,
    dense_rank() OVER (ORDER BY first_value)
FROM (
    SELECT
       *,
       first_value(id) OVER (PARTITION BY value ORDER BY id)
    FROM
       mytable
) s