asked on

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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.

Kyle Abrahams, PMP

Misread your code as a nested query. Lead would definitely be the way to go.

awking00

What is the data type of "Course Year"?

Coco Beans

ASKER

It’s a number - 2018 etc

slightwv (䄆 Netminder)

>>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.

Coco Beans

ASKER

Yes it’s a number field. Thanks

slightwv (䄆 Netminder)

Have you tried they query I posted?

Did it work? If not, please provide more sample data showing why it didn't work.

Coco Beans

ASKER

Thank you slightwv. Worked great