Member Avatar for pixfield

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
  • 141 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by timothybard
Member Avatar for timothybard

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.