aspose file tools*
The moose likes JDBC and the fly likes sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql" Watch "sql" New topic
Author

sql

Jackie Wang
Ranch Hand

Joined: Apr 18, 2002
Posts: 315
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
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1752
    
    2
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
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: 1752
    
    2
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: 1752
    
    2
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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql