Jackie Wang

Ranch Hand

Posts: 315

posted 14 years ago

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

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

Michael Matola

whippersnapper

Ranch Hand

Ranch Hand

Posts: 1838

5

posted 14 years ago

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 =

[ February 05, 2003: Message edited by: Michael Matola ]

*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

Posts: 315

Michael Matola

whippersnapper

Ranch Hand

Ranch Hand

Posts: 1838

5

posted 14 years ago

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

Ranch Hand

Posts: 1838

5

posted 14 years ago

No problem. This kind of query is tricky to understand until you've done it yourself a few times.

You said

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 #

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

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 it will return

2 3 1999

for the first table and

2 4 2000

for the second table.

Let's use the

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

Starting to make sense?

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?