CODEWITHSUNDEEP
sqlpostgresql
MoonlightSolace
MoonlightSolace

Reputation: 25

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

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.

Upvotes: 2

Views: 1157

Answers (1)

S-Man
S-Man

Reputation: 23726

demo:db<>fiddle

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

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

Upvotes: 4

Related Questions