This content has been marked as final. Show 3 replies
tem wrote:ROWNUM is assigned as rows are fetched and considered for inclusion in the result set. If the row is not chosen for any reason, the same ROWNUM will be reused with the next row fetched. ROWNUM=2 will not be assigned until a row with ROWNUM=1 has been included in hte result set.
... So, wouldn't setting rownum = 2 return john, and rownum = 3 return sue? For example,, ROWNUM
WHERE datetime = ( select min(datetime) from my_table )
AND rownum = 2;
return no rows. I just want to make sure I'm understanding how the select statement above works. It seems to work fine for returning one row having the minimum date and time. If this is always the case, then everything is fine. But I wouldn't have expected it not to return one of the other rows when rownum is 2 or 3, which makes me question why? Maybe I can learn something here. Any comments much appreciated.
So, in your example:
Say the first row that happens to be fetched has firstname='ken'. It is assigned ROWNUM=1, and fails the WHERE clause condition "WHERE rownum = 2".
SELECT * FROM my_table WHERE datetime = ( select min(datetime) from my_table ) AND rownum = 2;
Say the next row fetched has firstname='john'. ROWNUM=1 hasn't been used yet, so this row is also assigned ROWNUM=1, and it fails the WHERE clause for the same reason. Likewise with the next row; it also is assigned ROWNUM=1, and it also fails.
When using ROWNUM in a WHERE clause, you almost always want to say "ROWNUM = 1" or "ROWNUM <= n".
You could also use the analytic ROW_NUMBER function:
Here, all values of r_num are available, so it would make sense to say things like "WHERE r_num = 2" or "WHERE r_num >= 2".
WITH got_r_num AS ( SELECT datetime, firstname , ROW_NUMBER () OVER (ORDER BY datetime) AS r_num FROM my_table ) SELECT datetime, firstname FROM got_r_num WHERE r_num = 1 ;
Edited by: Frank Kulash on Apr 2, 2012 5:31 PM
Added to explanation.
So, wouldn't setting rownum = 2 return john, and rownum = 3 return sue?
No - see ROWNUM Pseudocolumn in the SQL Language doc
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.