if I want the date with lowest version # which is belong to stmt 2. How should I write my sql? i.e. i need date = 1999 in 1st one i need date = 2000 in 2nd table stmt can either be 1 or 2 version is ascending. stmt version date ==== ======= ==== 1 1 1997 1 2 1998 2 3 1999 stmt version date ==== ======= ==== 1 1 1997 1 2 1998 1 3 1999 2 4 2000 2 5 2001

Assuming that stmt is a key field (it's helpful to point out keys when asking this sort of question), the most generalized way of handling this is

You can add additional conditions (j.stmt = 2, for example) to the outer query. The overall effect of the correleated subquery that joins j2.stmt = j.stmt is to create "groups" by stmt, then pick the minimum version for each group. The outer qualification j.version = ( correlated subquery ) picks the one row for each stmt that has the minimum version. [ February 05, 2003: Message edited by: Michael Matola ]

Jackie Wang
Ranch Hand

Joined: Apr 18, 2002
Posts: 315

posted

0

thanks michael. but i am a little bit confused about the outer qualification j.version sorry for being slow

Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746

2

posted

0

I just dug up an old post of mine that goes into a little more detail about this general category of SQL queries.

Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746

2

posted

0

No problem. This kind of query is tricky to understand until you've done it yourself a few times. You said if I want the date with lowest version # which is belong to stmt 2. Forget for a second that you're only asking about statment 2 and let's solve the more general problem: I want the date with the lowest version # for each statement. In your first table that would be the rows 1 1 1997 2 3 1999 And in the second table 1 1 1997 2 4 2000 The query I posted above solves this more general problem. If you're only interested in statement 2, you can rewrite the query with an extra condition: select j.stmt , j.version , j.dt from jackie j where j.version = ( select min( j2.version ) from jackie j2 where j2.stmt = j.stmt ) and j.stmt = '2'

And it will return 2 3 1999 for the first table and 2 4 2000 for the second table. Let's use the second table of yours and my revised query (with and j.stmt = '2') to figure out how this works. What I'm going to describe is not necessarily how the datatbase goes about performing the logic, but just one way of thinking about it. Take the rows of your second table one by one. First row ( 1 1 1997 ) -- fails the condtion of j.stmt = '2', so it's discarded Second row ( 1 2 1998 ) -- fails the condtion of j.stmt = '2', so it's discarded Third row ( 1 3 1999 ) -- fails the condtion of j.stmt = '2', so it's discarded Fourth row ( 2 4 2000 ) -- stmt equals 2, so we're OK on that condition, but we need to test the other condition. The inner query is going to return "4" because it pulls the minimum value of version based on the the group of all rows where statement equals 2. The inner query is looking at rows where statement equals 2 because that's the value of statement in the row the outer select is currently looking at and we've set told it to look at rows where j2.stmt = j.stmt. So the inner query looks across these 2 rows where statement equals 2 2 4 2000 2 5 2001 And finds the minimum version to be equal to 4. That's what the subquery returns, so the fourth row meets both conditions in the outer select and is retained in the results. Fifth row ( 2 5 2001 ) -- stmt equals 2, so we're OK on that condition, but we need to test the other condition. The inner query is going to return "4" because it pulls the minimum value of version based on the the group of all rows where statement equals 2. The inner query is looking at rows where statement equals 2 because that's the value of statement in the row the outer select is currently looking at and we've set told it to look at rows where j2.stmt = j.stmt. So the inner query looks across these 2 rows where statement equals 2 2 4 2000 2 5 2001 And finds the minimum version to be equal to 4. That's what the subquery returns, so the fifth row *fails* the condition of j.version = correlated subquery. The version number in the row the outer select is currently looking is 5 but the value returned by the subquery is 4. So the row is discarded. Starting to make sense?