How do I use ROW_NUMBER()?

Question

I want to use the ROW_NUMBER() to get...

To get the max(ROW_NUMBER()) --> Or I guess this would also be the count of all rows

I tried doing:

SELECT max(ROW_NUMBER() OVER(ORDER BY UserId)) FROM Users

but it didn't seem to work...

To get ROW_NUMBER() using a given piece of information, ie. if I have a name and I want to know what row the name came from.

I assume it would be something similar to what I tried for #1

SELECT ROW_NUMBER() OVER(ORDER BY UserId) From Users WHERE UserName='Joe'

but this didn't work either...

Any Ideas?

Answer 1

For the first question, to get the count, you can use the below code:

SELECT COUNT(*) FROM myTable 

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 have returned the Row_Number() in your main query like this:

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

FROM User

Then, if you want to go 5 rows back then you should take the current row number and use the below query to determine the row with current row -5 this way:

SELECT us.Id

FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS Row, Id

FROM User ) us 

WHERE Row = CurrentRow - 5