Friday, June 18, 2010

ADS 10 Tip # 15 - ROWNUM

Another mechanism for doing paging with SQL is to use the ROWNUM function. ROWNUM assigns a unique number to each row as it is added to the resultset. See the example below.

SELECT ROWNUM() as ID, LastName, FirstName, CompanyName FROM CustomerList
WHERE State = 'CA'

Rownum1 

As you can see we get a unique number for each row that is returned. It is important to remember that the row number is assigned as the rows are added to the resultset. If you are using an ORDER BY clause the row numbers might not be in numerical order since the order may be applied after the resultset is populated. If there is an index that matches the ORDER BY then the results can be ordered prior to being added to the resultset. If no index exists then the order will be applied after the results have been added to the resultset as demonstrated below.

SELECT ROWNUM() as ID, LastName, FirstName, CompanyName FROM CustomerList
WHERE State = 'CA' ORDER BY LastName

Rownum2

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

No comments: