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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kyle AbrahamsSenior .Net DeveloperCommented:
Misread your code as a nested query.  Lead would definitely be the way to go.
0
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.