0

I work at a college and we have a file that looks like this:

ID DEGREE
0001 BA
0002 BS
0002 BA
0003 BA
0003 BA
0004 BS
0005 BS
0005 BA
0006 BA
0006 BA
0007 BS
0007 AA
0007 BA

Each student has a unique ID and could have recieved 1 or more DEGREEs. I need to find out how many unique degrees have been granted. I know I can easily do this using DISTINCT in my SQL code. However, I want to show the record that has the BA degree in preference to the BS which is in preference to the AA degree. In other words, I want to do a DISTINCT with the rows being displayed based upon the hierarchy of degrees.

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by timothybard
0

Try something like this:

SELECT ID, Min([Degree]="BA") AS [HAS BA], Min([Degree]="BS") AS [HAS BS], Min([Degree]="AA") AS [HAS AA], IIf([HAS BA],"BA",IIf([HAS BS],"BS",IIf([Has AA],"AA"))) AS HighestDegree
FROM StudentDegrees
GROUP BY ID;

The middle three fields find out which degrees the student has and the last field shows the "highest" degree the student earned.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.