Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE school_id_cards (
  card_id int NOT NULL
, school_id int NOT NULL
, student_id int NOT NULL
);

INSERT INTO school_id_cards VALUES
  ( 1, 1, 1)
, ( 2, 1, 1)
, ( 3, 1, 2)
, ( 4, 1, 2)
, ( 5, 1, 3)
, ( 6, 1, 3)
, ( 7, 1, 4)
, ( 8, 1, 4)
, ( 9, 2, 5)
, (10, 2, 5)
, (11, 2, 6)
, (12, 3, 7)
;

-- essential for performance !
CREATE INDEX ON school_id_cards (school_id DESC, card_id DESC);


-- should exist ?!
CREATE TABLE school (
  school_id int PRIMARY KEY
, school text NOT NULL   
);

INSERT INTO school VALUES
  (1, 'school 1')
, (2, 'school 2')
, (3, 'school 3')
;

CREATE TABLE



INSERT 0 12



CREATE INDEX



CREATE TABLE



INSERT 0 3


-- With existing "school" table
WITH RECURSIVE latest_card AS (
   SELECT c.*
   FROM   school s
   CROSS  JOIN LATERAL (
      SELECT c.school_id, c.card_id, ARRAY[c.student_id] AS leading_ids
      FROM   school_id_cards c
      WHERE  c.school_id = s.school_id
      ORDER  BY c.card_id DESC
      LIMIT  1
      ) c

   UNION ALL
   SELECT c.*
   FROM   latest_card l
   JOIN   LATERAL (
      SELECT l.school_id, c.card_id, l.leading_ids || student_id
      FROM   school_id_cards c
      WHERE  c.school_id = l.school_id
      AND    c.card_id < l.card_id
      AND    c.student_id <> ALL (l.leading_ids)
      ORDER  BY c.card_id DESC
      LIMIT  1
      ) C ON cardinality(l.leading_ids) < 2  -- your limit per school here!
   )
SELECT card_id
FROM   latest_card
ORDER  BY card_id;


card_id


6

8

10

11

12



SELECT 5


-- If you don't have some "school" table (really?)
WITH RECURSIVE latest_card AS (
   (
   SELECT DISTINCT ON (school_id)
          school_id, card_id, ARRAY[student_id] AS leading_ids
   FROM   school_id_cards c
   ORDER  BY school_id DESC, card_id DESC
   )

   UNION ALL
   SELECT c.*
   FROM   latest_card l
   JOIN   LATERAL (
      SELECT l.school_id, c.card_id, l.leading_ids || student_id
      FROM   school_id_cards c
      WHERE  c.school_id = l.school_id
      AND    c.card_id < l.card_id
      AND    c.student_id <> ALL (l.leading_ids)
      ORDER  BY c.card_id DESC
      LIMIT  1
      ) C ON cardinality(l.leading_ids) < 2  -- your limit per school here!
   )
SELECT card_id
FROM   latest_card
ORDER  BY card_id;


card_id


6

8

10

11

12



SELECT 5