SQL query with unique incrementing id for each group of rows (Postgresql)

Question

I have a table student with an identifying column name of type TEXT. I'm looking to have a query that deterministically outputs an incrementing id for each group of unique name. For example:

------+----------
 name | group_id
------+----------
  A   |    1
  A   |    1
  B   |    2
  C   |    3
  D   |    4
  D   |    4
  D   |    4

I've looked at windowing functions in PSQL (currently running PostgreSQL 13.2) but no luck. Feel like it's a simple thing to do, but can't wrap my head around it.

user_3984221 · Answer

demo:db<>fiddle

That's exactly what dense_rank() window function does.

SELECT
    *,
    dense_rank() OVER (ORDER BY name) as group_id
FROM t

SQL query with unique incrementing id for each group of rows (Postgresql)

1 Answers