With subquery I need to select after first five rows for each group of id_student and must common values of id_desireCollage between id_student.

More explain : select common collages for each student desires after his five chosen desires

Something like

select id_desireCollage 
from
    (select * 
     from desires ds 
     where ds.id_desireCollage = desires.id_desireCollage) 
group by (id_student) 
having count(*) > 5

Expected result is:

CodePudding user response：

Try the following:

select id_desireCollage
from
(
  select d.*,
    row_number() over (partition by id_student order by ID) as rn
  from desires d
) T
where rn > 5
group by id_desireCollage
order by count(*) desc
fetch first 1 row with ties

If you don't want to use the row number function (as you commented), you may try the following - supposing there are no gaps in the ID column:

select id_desireCollage
from desires d
where id >= 
  (
    select min(id) 5
    from desires t 
    where t.id_student = d.id_student
  )
group by id_desireCollage
order by count(*) desc
fetch first 1 row with ties

See demo

As suggested by @MatBailie, if you meant by common, that all students have selected the id_desireCollage value then you could use the following:

select id_desireCollage
from desires d
where id >= 
  (
    select min(id) 5
    from desires t 
    where t.id_student = d.id_student
  )
group by id_desireCollage
having count(*)=
(
  select count(distinct id_student)
  from desires 
)