afair afair - 2 months ago 11
SQL Question

How to Number Each Group in PostgreSQL

How would I use a window function or similar, to number each group or partition of rows, based on certain shared characteristics?

For example:

I have a list of names ordered alphabetically that I wish to group and identify using IDs that describe the group that they belong to and position within each group.

-------------------------------------------
| outer_id | inner_id | src_id | name |
|----------|----------|--------|----------|
| 1 | 1 | 88129 | albert |
| 1 | 2 | 88130 | albrecht |
| 1 | 3 | 88131 | allan |
| 2 | 1 | 88132 | barnaby |
| 2 | 2 | 88133 | barry |
| 2 | 3 | 88134 | bart |
-------------------------------------------


I can achieve
inner_id
,
src_id
and
name
using a query similar to the following:

WITH cte (src_id, name) AS (

VALUES

(88129, 'albert'),
(88130, 'albrecht'),
(88131, 'allan'),
(88132, 'barnaby'),
(88133, 'barry'),
(88134, 'bart')

)

SELECT row_number() OVER (partition by left(name, 1) ORDER BY name DESC) AS inner_id, src_id, name
FROM cte;


How would I go about adding an
outer_id
column as shown, to represent each window (or group)?

Answer Source

You can use dense_rank():

select dense_rank() over (order by left(name, 1)) as outer_id,
       row_number() over (partition by left(name, 1) order by name desc) as inner_id,
       src_id, name
from cte;