create table student_subject (
id number(4),
student_id varchar2(20),
subject_name varchar2(20),
part varchar2(20)
);
insert into student_subject values (1,'std1','Bengali','part1');
insert into student_subject values (2,'std1','English','part1');
insert into student_subject values (3,'std1','English','part2');
insert into student_subject values (4,'std2','Bengali','part1');
insert into student_subject values (5,'std3','Bengali','part1');
insert into student_subject values (6,'std3','English','part1');
insert into student_subject values (7,'std3','English','part2');
insert into student_subject values (8,'std3','English','part3');
insert into student_subject values (9,'std4','Bengali','part1');
insert into student_subject values (10,'std4','English','part1');
insert into student_subject values (11,'std4','Math','part1');
insert into student_subject values (12,'std5','Bengali','part1');
insert into student_subject values (13,'std5','Math','part1');
In my report I have to pull all students with the subject "English", if available. If no "English", then pulling "Bengali".
Here "Bengali" is there mandatory subject and every student has "Bengali".
If multiple "English" available for each student then all rows should come.
SELECT student_id,subject_name, part
FROM (
SELECT DISTINCT ss.student_id
, ss.subject_name
, part
, RANK() OVER (PARTITION BY ss.student_id ORDER BY decode(ss.subject_name, 'English', 1, 'Bengali', 2, 3)) rn
FROM student_subject ss
WHERE ss.subject_name in ('Bengali','English')
) WHERE rn=1
ORDER BY student_id,part
;
Output
std1 English part1
std1 English part2
std2 Bengali part1
std3 English part1
std3 English part2
std3 English part3
std4 English part1
std5 Bengali part1
In my report I have to pull all students with the subject "English", if available. If no "English", then pulling "Bengali".
Here "Bengali" is there mandatory subject and every student has "Bengali".
If multiple "English" available for each student then one row should come.
SELECT student_id,subject_name,part
FROM (
SELECT DISTINCT ss.student_id
, ss.subject_name
, part
, ROW_NUMBER() OVER (PARTITION BY ss.student_id ORDER BY decode(ss.subject_name, 'English', 1, 'Bengali', 2, 3)) rn
FROM student_subject ss
WHERE ss.subject_name in ('Bengali','English')
) WHERE rn=1
ORDER BY student_id
;
Output
std1 English part1
std2 Bengali part1
std3 English part1
std4 English part1
std5 Bengali part1
id number(4),
student_id varchar2(20),
subject_name varchar2(20),
part varchar2(20)
);
insert into student_subject values (1,'std1','Bengali','part1');
insert into student_subject values (2,'std1','English','part1');
insert into student_subject values (3,'std1','English','part2');
insert into student_subject values (4,'std2','Bengali','part1');
insert into student_subject values (5,'std3','Bengali','part1');
insert into student_subject values (6,'std3','English','part1');
insert into student_subject values (7,'std3','English','part2');
insert into student_subject values (8,'std3','English','part3');
insert into student_subject values (9,'std4','Bengali','part1');
insert into student_subject values (10,'std4','English','part1');
insert into student_subject values (11,'std4','Math','part1');
insert into student_subject values (12,'std5','Bengali','part1');
insert into student_subject values (13,'std5','Math','part1');
| ID | STUDENT_ID | SUBJECT_NAME | PART |
| 1 | std1 | Bengali | part1 |
| 2 | std1 | English | part1 |
| 3 | std1 | English | part2 |
| 4 | std2 | Bengali | part1 |
| 5 | std3 | Bengali | part1 |
| 6 | std3 | English | part1 |
| 7 | std3 | English | part2 |
| 8 | std3 | English | part3 |
| 9 | std4 | Bengali | part1 |
| 10 | std4 | English | part1 |
| 11 | std4 | Math | part1 |
| 12 | std5 | Bengali | part1 |
| 13 | std5 | Math | part1 |
In my report I have to pull all students with the subject "English", if available. If no "English", then pulling "Bengali".
Here "Bengali" is there mandatory subject and every student has "Bengali".
If multiple "English" available for each student then all rows should come.
SELECT student_id,subject_name, part
FROM (
SELECT DISTINCT ss.student_id
, ss.subject_name
, part
, RANK() OVER (PARTITION BY ss.student_id ORDER BY decode(ss.subject_name, 'English', 1, 'Bengali', 2, 3)) rn
FROM student_subject ss
WHERE ss.subject_name in ('Bengali','English')
) WHERE rn=1
ORDER BY student_id,part
;
Output
std1 English part1
std1 English part2
std2 Bengali part1
std3 English part1
std3 English part2
std3 English part3
std4 English part1
std5 Bengali part1
In my report I have to pull all students with the subject "English", if available. If no "English", then pulling "Bengali".
Here "Bengali" is there mandatory subject and every student has "Bengali".
If multiple "English" available for each student then one row should come.
SELECT student_id,subject_name,part
FROM (
SELECT DISTINCT ss.student_id
, ss.subject_name
, part
, ROW_NUMBER() OVER (PARTITION BY ss.student_id ORDER BY decode(ss.subject_name, 'English', 1, 'Bengali', 2, 3)) rn
FROM student_subject ss
WHERE ss.subject_name in ('Bengali','English')
) WHERE rn=1
ORDER BY student_id
;
Output
std1 English part1
std2 Bengali part1
std3 English part1
std4 English part1
std5 Bengali part1
