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
);

CREATE TABLE


insert into school_id_cards(card_id, school_id, student_id) 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);

INSERT 0 12


select 
  *,
  dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order
from school_id_cards
-- the order by is only to make the results easier to read
order by school_id, student_id


card_id
school_id
student_id
student_card_order


2
1
1
1

1
1
1
2

4
1
2
1

3
1
2
2

6
1
3
1

5
1
3
2

8
1
4
1

7
1
4
2

10
2
5
1

9
2
5
2

11
2
6
1

12
3
7
1



SELECT 12


with ordered_student_cards as (
  select 
    *,
    dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order
  from school_id_cards
)
select
  *,
  dense_rank() over(partition by school_id order by card_id desc) as school_card_order
from ordered_student_cards
where student_card_order = 1
-- the order by is only to make the results easier to read
order by school_id, student_id


card_id
school_id
student_id
student_card_order
school_card_order


2
1
1
1
4

4
1
2
1
3

6
1
3
1
2

8
1
4
1
1

10
2
5
1
2

11
2
6
1
1

12
3
7
1
1



SELECT 7


-- We can't do it in one query. Note how the results are different.
select 
  *,
  dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order,
  dense_rank() over(partition by school_id order by card_id desc) as school_card_order
from school_id_cards
order by school_id, student_id


card_id
school_id
student_id
student_card_order
school_card_order


2
1
1
1
7

1
1
1
2
8

4
1
2
1
5

3
1
2
2
6

6
1
3
1
3

5
1
3
2
4

8
1
4
1
1

7
1
4
2
2

10
2
5
1
2

9
2
5
2
3

11
2
6
1
1

12
3
7
1
1



SELECT 12


with ordered_student_cards as (
  select 
    *,
    dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order
  from school_id_cards
), ordered_school_cards as (
  select
    *,
    dense_rank() over(partition by school_id order by card_id desc) as school_card_order
  from ordered_student_cards
  where student_card_order = 1
)
select card_id
from ordered_school_cards
where school_card_order <= 2;


card_id


8

6

11

10

12



SELECT 5


with ordered_cards as (
  select 
    *,
    dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order,
    dense_rank() over(partition by school_id order by card_id desc) as school_card_order
  from school_id_cards
)
select *
from ordered_cards
where school_card_order <= 2;


card_id
school_id
student_id
student_card_order
school_card_order


8
1
4
1
1

7
1
4
2
2

11
2
6
1
1

10
2
5
1
2

12
3
7
1
1



SELECT 5

card_id	school_id	student_id	student_card_order	school_card_order
2	1	1	1	7
1	1	1	2	8
4	1	2	1	5
3	1	2	2	6
6	1	3	1	3
5	1	3	2	4
8	1	4	1	1
7	1	4	2	2
10	2	5	1	2
9	2	5	2	3
11	2	6	1	1
12	3	7	1	1

card_id	school_id	student_id	student_card_order	school_card_order
2	1	1	1	7
1	1	1	2	8
4	1	2	1	5
3	1	2	2	6
6	1	3	1	3
5	1	3	2	4
8	1	4	1	1
7	1	4	2	2
10	2	5	1	2
9	2	5	2	3
11	2	6	1	1
12	3	7	1	1

card_id	school_id	student_id	student_card_order	school_card_order
2	1	1	1	7
1	1	1	2	8
4	1	2	1	5
3	1	2	2	6
6	1	3	1	3
5	1	3	2	4
8	1	4	1	1
7	1	4	2	2
10	2	5	1	2
9	2	5	2	3
11	2	6	1	1
12	3	7	1	1