On 10/14/06, Will Leshner <[EMAIL PROTECTED]> wrote:
On 10/13/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> Having a builtin function that returns the row number of the row in
> the result set would be useful for some of the things I'm doing. Rowid
> is close, but not won't work in my application.
What would the "row number" represent?
The offset of the row within the result set (0,1,2,3 etc). You can get
it programmatically but it your're trying to do something complicated
all in SQL you don't have that option.
I believe Oracle provides it after 8i:
( http://www.orafaq.com/faqsql.htm )
One can easily select all even, odd, or Nth rows from a table using
SQL queries like this:
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4) FROM emp);
Form Oracle8i one can have an inner-query with an ORDER BY clause.
Look at this example:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
