Select id_student from each group id_student where id_desireCollage=5 not in first 5 rows student id.

ID	id_desireCollage	id_student
1	1	1
2	2	1
3	3	1
4	4	1
6	5	1
7	2	2
8	5	2
9	1	2
10	3	2
11	7	2
12	4	3
13	3	3
14	2	3
15	1	3
16	8	3
17	9	3
18	7	3
19	5	3

I tried:

select id_student
from   student_desire
group by(id_student)
having id_desireCollage not in first 5 rows

Expected result is:

id_student
3

CodePudding user response：

You can use ROW_NUMBER(). For example:

select id_student
from (
  select t.*, row_number() over(partition by id_student order by id) as rn 
  from t
) x
where id_desireCollage = 5 and rn > 5

CodePudding user response：

Assuming the IDs are sequential within the group, you could query students where the difference between the ID of id_desireCollage=5 and the first id_desireCollage is more than 5:

SELECT   id_student
FROM     student_desire
GROUP BY id_student
HAVING   MIN(CASE id_desireCollage WHEN 5 THEN id END) - MIN(id) > 5

CodePudding user response：

You can use the ROW_NUMBER analytic function:

SELECT id_student
FROM   (
  SELECT id_student,
         id_desireCollage,
         ROW_NUMBER() OVER (PARTITION BY id_student ORDER BY id) AS rn
  FROM   student_desire
)
WHERE  id_desireCollage = 5
AND    rn > 5;

or, from Oracle 12, (without any functions) using MATCH_RECOGNIZE:

SELECT id_student
FROM   student_desire
MATCH_RECOGNIZE(
  PARTITION BY id_student
  ORDER BY id
  PATTERN (^ not5{5,} id5)
  DEFINE
    not5 AS id_desireCollage != 5,
    id5  AS id_desireCollage = 5
);

Which, for the sample data:

CREATE TABLE student_desire (ID, id_desireCollage, id_student) AS
  SELECT 1, 1, 1 FROM DUAL UNION ALL
  SELECT 2, 2, 1 FROM DUAL UNION ALL
  SELECT 3, 3, 1 FROM DUAL UNION ALL
  SELECT 4, 4, 1 FROM DUAL UNION ALL
  SELECT 6, 5, 1 FROM DUAL UNION ALL
  SELECT 7, 2, 2 FROM DUAL UNION ALL
  SELECT 8, 5, 2 FROM DUAL UNION ALL
  SELECT 9, 1, 2 FROM DUAL UNION ALL
  SELECT 10, 3, 2 FROM DUAL UNION ALL
  SELECT 11, 7, 2 FROM DUAL UNION ALL
  SELECT 12, 4, 3 FROM DUAL UNION ALL
  SELECT 13, 3, 3 FROM DUAL UNION ALL
  SELECT 14, 2, 3 FROM DUAL UNION ALL
  SELECT 15, 1, 3 FROM DUAL UNION ALL
  SELECT 16, 8, 3 FROM DUAL UNION ALL
  SELECT 17, 9, 3 FROM DUAL UNION ALL
  SELECT 18, 7, 3 FROM DUAL UNION ALL
  SELECT 19, 5, 3 FROM DUAL

All output:

ID_STUDENT
3

---

You can do it without analytic functions or MATCH_RECOGNIZE, using a correlated sub-query and aggregation functions instead, but its (much) less efficient (and does use the COUNT aggregation function):

SELECT id_student
FROM   student_desire s
WHERE  id <= ( SELECT id
               FROM   student_desire c
               WHERE  s.id_student = c.id_student
               AND    c.id_desireCollage = 5 )
GROUP BY id_student
HAVING COUNT(*) > 5

fiddle