Search

Custom Search

Monday, March 10, 2008

Selecting specific Row and Size in a record using ROW_NUMBER()

Sometime we want to select row that is between a record like for example record 10 to 20. We cannot achieve that by using TOP for it will just get the records starting from top. We can write it like this:
SELECT TOP 20 * FROM TABLE1 WHERE FIELDID NOT IN (SELECT TOP 10 FIELDID FROM TABLE1)
But performance wise this is not advisable. I recommend using ROW_NUMBER() OVER function in MSSQL to assign ROWID from the table then selecting the ROW in where clause. Here is the syntax below:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderField) as row FROM Table1) a WHERE row > (@RecordNo-1) and row <= (@RecordNo + @RecordSize-1)

where:
@RecordNo is the starting row
@RecordSize is the no of rows you want to retrieve starting from @RecordNo
OrderField is the order you want for the record before extraction

So to select records 10 to 20 you write it as:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderField) as row FROM Table1) a WHERE row > (9) and row <= (19)

1 comment:

Adsense Banner