[thelist] Oracle: rownum tip

[Adam Patrick]

here's something interesting:  I just got this e-mail from someone at
Ariba.com *out of the blue* asking a question about Oracle and rownums. 
so I asked her how she got my name and e-mail address thinking "how did
she know I new something about Oracle?".  so she said she was searching
on google.com and got it that way so I went and did my own search and
found a bunch of my posts from evolt.  interesting.  

so her question got me to thinking and looking at the Oracle docs yada,
yada, yada....  She wanted to know if she could use the rownum
pseudocolumn to get rows 100-120 of a query.  I explained how that
didn't work but I remembered another question about getting rows 1-20
*after* sorting.  Rownum used to only work *before* sorting...but a new
feature in Oracle8 called in-line views allows you to get the first 20
rows *after* the sort.

<tip type="Oracle trick">
Use an in-line view to grab the first X rows of a query *after* the
sort.  Example:

select *
  from ( select a, b, c from foo where a = 1 order by a ) foo
 where rownum <= 20

note: This still won't work to get rows X-Y.  If you want to get rows
20-40 for instance, you'll still have to do it manually or using the
wonderful features of your language of choice.
</tip>

-- 
_______________________________________________________________
Believe half of what you see and none of what you hear.