Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE persons 
(
  person_id   INT, 
  person_name TEXT
);

CREATE TABLE


create table places 
(
  place_id   INT, 
  person_id  INT, 
  place_name TEXT
);

CREATE TABLE


INSERT INTO persons VALUES 
(10, 'Aulus Agerius'), (20, 'Numerius Negidius');

INSERT 0 2


INSERT INTO places VALUES 
(10, 10, 'Anytown'), (20, 10, 'Timbuktu'), (30, 20, 'Podunk');

INSERT 0 3


SELECT 
  person_name, place_name
FROM 
  persons 
JOIN
  places using (person_id)
ORDER BY person_id, place_id;


person_name
place_name


Aulus Agerius
Anytown

Aulus Agerius
Timbuktu

Numerius Negidius
Podunk



SELECT 3


--
-- Using (PARTITION BY person_id ORDER BY place_id)
--
-- Just to be explicit - you could **_possibly_** have two people and places with 
-- the same name but different ids - it's a belt and braces approach, but better
-- safe than sorry - without this, you could get very difficult to diagnose errors
-- in your results.
--

SELECT 
  person_id,
  place_id,
  person_name, 
  place_name, 
  ROW_NUMBER() 
    OVER (PARTITION BY person_id ORDER BY place_id) AS rn
FROM persons 
  JOIN places using (person_id)
ORDER BY person_id, place_id;


person_id
place_id
person_name
place_name
rn


10
10
Aulus Agerius
Anytown
1

10
20
Aulus Agerius
Timbuktu
2

20
30
Numerius Negidius
Podunk
1



SELECT 3


SELECT 
  person_name, 
  place_name, 
  ROW_NUMBER() 
    OVER (PARTITION BY person_id ORDER BY place_id) AS rn
FROM persons 
  JOIN places using (person_id)
ORDER BY person_id, place_id;


person_name
place_name
rn


Aulus Agerius
Anytown
1

Aulus Agerius
Timbuktu
2

Numerius Negidius
Podunk
1



SELECT 3
person_name	place_name
Aulus Agerius	Anytown
Aulus Agerius	Timbuktu
Numerius Negidius	Podunk