MVSFORUMS.com

[WHITE]

Hi, I was practising on SQL to improve my grip over that. In that respect, I am trying to write a Corelated subquery, Following is the Query

SELECT
TA.REQUEST_NUM
FROM
ABCK.WO_REQUEST TA
WHERE 10 = (SELECT COUNT (TB.WO_REQUEST_NUM)
FROM
ABCK.WO_REQUEST TB
WHERE TA.WO_REQUEST_NUM > TB.WO_REQUEST_NUM) ;

I was expecting 10 Highest number of rows from the above query, But I was getting only one row from this.

When I changed the Inner select where clause to the following, I am still getting one row but a different than what I got earlier.

TB.WO_REQUEST_NUM > TA.WO_REQUEST_NUM


My assumption is that, for each row of the outer table , we will scan all the rows of the inner table (Though both are one table. DB2 will treat that as two tables as it will make two copies of the same table) and collect 10 maximum rows and display that on the screen.

Is my assumption right ?

Could you some one throw light on this one?