asked on

WHERE = (value in from a subselect)....... (This SQL works, just want your opinion on it ) Part 2

Hi EE,

In a related question, and EE expert taught me how to improve my sql when testing a WHERE to a subselect.

I am in a similar situation,however:
in the prev post, i wanted the max for a student's id

It's possible for a student to complete multiple degrees, for ex:
MED MD DEGREE 05-31-13
PH PHD DEGREE 12-31-12

Both degrees can have diff. graduation dates. The vendor's pgm allows the user to go in an even generate multiple
degree rows (don't know why that would happen) and assigns a seq # like this:

MED MD DEGREE 05-31-13 1
PH PHD DEGREE 12-31-12 1
PH PHD DEGREE 12-31-12 2

so i want the graduation date for the max seq no like this

MED MD DEGREE 05-31-13 1
PH PHD DEGREE 12-31-12 2

So i figured, would have to group by student_id, College, Degree

and then i correlated like this:
WHERE b.person_uid = a.person_uid
and
b.college = a.college
and
b.program = a.program

I'd like to double check with you that coding the above WHERE in the sub select was the right approach.
I think so, bec. got desired results...

Below are screen shots:
a) detail rows for a student (student id is not shown as one of the columns)
b) results of the sql that appears in the code window below...

tx for your help, sandra

SELECT   a.person_uid
        ,a.college
        ,a.program
        ,a.curriculum_order
        ,a.outcome_graduation_date
FROM academic_outcome a
WHERE a.person_uid = 18429
            AND     
      a.curriculum_order =   
         (
          select max(curriculum_order)
          from academic_outcome b
          WHERE b.person_uid = a.person_uid
                         and
                b.college = a.college
                         and
                b.program = a.program         
          group by person_uid, college, program
         )

Open in new window

2010-11-30a-adg-dtl.GIF
2010-11-30b-adg-dtl.GIF

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

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

Start Free Trial

mytfein

ASKER

Thx Angel,

tx for writing and your validation, s

Muhammad Ahmad Imran

does not seems any problem

gatorvip

PH PHD DEGREE 12-31-12 1
PH PHD DEGREE 12-31-12 2

How are these two ordered - what decides whether it's a "1" or a "2"?

You could accomplish what you want with analytics. Something like the code below should work:

SELECT   person_uid
        ,college
        ,program
        ,curriculum_order
        ,outcome_graduation_date
from        
(
	SELECT   a.person_uid
		,a.college
		,a.program
		,a.curriculum_order
		,a.outcome_graduation_date
		,row_number() over(partition by person_uid, college, program order by curriculum_order desc) rn
	FROM academic_outcome a
	WHERE a.person_uid = 18429
)
where rn=1

Open in new window

mytfein

ASKER

Hi Leo and Gator,

tx for writing, sorry that i closed the question as done while you were replying....

Hi Gator,
i understand your suggestion as another approach.... i could sort by approp column so that
the correct row gets tagged with row#1 for rn=1

tx again everyone, s