Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE my_table (
    pk int,
    name text
);

INSERT INTO my_table VALUES
(1, 'notebook cz-2001'),
(2, 'notebook cz-2002'),
(3, 'notebook cz-2003'),
(4, 'notebook cz-2003'),
(5, 'notebook cz-2003'),
(6, 'notebook cz-2004'),
(7, 'notebook cz-2004'),
(8, 'notebook cz-2004'),
(9, 'notebook cz-2004'),
(10, 'notebook cz-2005'),
(11, 'notebook cz-2006'),
(12, 'notebook cz-2007'),
(13, 'notebook cz-2008'),
(14, 'notebook cz-2009');

SELECT * FROM my_table;
14 rows affected



pk
name


1
notebook cz-2001

2
notebook cz-2002

3
notebook cz-2003

4
notebook cz-2003

5
notebook cz-2003

6
notebook cz-2004

7
notebook cz-2004

8
notebook cz-2004

9
notebook cz-2004

10
notebook cz-2005

11
notebook cz-2006

12
notebook cz-2007

13
notebook cz-2008

14
notebook cz-2009


-- unsorted

SELECT
    *
FROM (
    SELECT
        *,
        row_number() OVER (PARTITION BY name ORDER BY pk DESC)
    FROM my_table
) s
WHERE row_number <= 2


pk
name
row_number


1
notebook cz-2001
1

2
notebook cz-2002
1

5
notebook cz-2003
1

4
notebook cz-2003
2

9
notebook cz-2004
1

8
notebook cz-2004
2

10
notebook cz-2005
1

11
notebook cz-2006
1

12
notebook cz-2007
1

13
notebook cz-2008
1

14
notebook cz-2009
1


-- sorted

SELECT
    *
FROM (
    SELECT
        *,
        row_number() OVER (PARTITION BY name ORDER BY pk DESC)
    FROM my_table
) s
WHERE row_number <= 2
ORDER BY pk


pk
name
row_number


1
notebook cz-2001
1

2
notebook cz-2002
1

4
notebook cz-2003
2

5
notebook cz-2003
1

8
notebook cz-2004
2

9
notebook cz-2004
1

10
notebook cz-2005
1

11
notebook cz-2006
1

12
notebook cz-2007
1

13
notebook cz-2008
1

14
notebook cz-2009
1

pk	name
1	notebook cz-2001
2	notebook cz-2002
3	notebook cz-2003
4	notebook cz-2003
5	notebook cz-2003
6	notebook cz-2004
7	notebook cz-2004
8	notebook cz-2004
9	notebook cz-2004
10	notebook cz-2005
11	notebook cz-2006
12	notebook cz-2007
13	notebook cz-2008
14	notebook cz-2009