Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

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
0
mytfein
Asked:
mytfein
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that is just fine, IMHO
with the indexes you seem to have, a nice explain plan should also ensure good execution
0
 
mytfeinAuthor Commented:
Thx Angel,

tx for writing and your validation, s
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
does not seems any problem
0
 
gatorvipCommented:
   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

0
 
mytfeinAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now