Return repeat Students

In oracle, I want to see people who have the same Course Description this year as they did last year

So students where  Last years Course = This Years Couse


Data is stored as follows:

Student ID: 111000
Course Code: 100
Course Year: 2018
Student ID: 111000
Course Code: 100
Course Year: 2017


Any help much appreciated.

Thanks
Coco BeansDesignerAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Try this:
select student_id, course_code from (
	select student_id,
		course_code,
		course_year,
		lead(course_year) over(partition by student_id, course_code order by student_id, course_code, course_year) next_year
	from tab1
)
where next_year-course_year=1;

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you could also do it with a join:
select t1.student_id, t1.course_code
from  tab1 t1
join tab1 t2 on t1.student_id = t2.student_id and t1.course_code = t2.course_code and t2.course_year = t1.course_year - 1
where 
t1.course_year = 2018

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
My guess is the final solution is more dynamic than hard coding 2018 and needs any consecutive years.

Also, why hit the table twice when you don't need to?  Granted in smaller tables it likely doesn't matter but it is a bad habit to get into when you start dealing with large tables.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
Misread your code as a nested query.  Lead would definitely be the way to go.
0
 
awking00Commented:
What is the data type of "Course Year"?
0
 
Coco BeansDesignerAuthor Commented:
It’s a number - 2018 etc
0
 
slightwv (䄆 Netminder) Commented:
>>It’s a number - 2018 etc

I believe awking00 is questioning the actual data type of the column in the table.  If it is a varchar2 then it is really a string holding numbers.

If it is a CHAR or VARCHAR2 then you should use TO_NUMBER around it in the queries when you perform math like subtraction.  It keeps Oracle from performing an implicit data type conversion.
0
 
Coco BeansDesignerAuthor Commented:
Yes it’s a number field. Thanks
0
 
slightwv (䄆 Netminder) Commented:
Have you tried they query I posted?

Did it work?  If not, please provide more sample data showing why it didn't work.
0
 
Coco BeansDesignerAuthor Commented:
Thank you slightwv.  Worked great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.