How do I use ROW_NUMBER()?

by

For the first question, why not just use?

SELECT COUNT(*) FROM myTable

to get the count.

And for the second question, the primary key of the row is what should be used to identify a particular row. Don’t try and use the row number for that.

If you returned Row_Number() in your main query,

SELECT ROW_NUMBER() OVER (Order by Id) AS RowNumber, Field1, Field2, Field3
FROM User

Then when you want to go 5 rows back then you can take the current row number and use the following query to determine the row with currentrow -5

SELECT us.Id
FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS Row, Id
     FROM User ) us 
WHERE Row = CurrentRow - 5

Leave a Comment

tech