sql - How do I use ROW_NUMBER()?

Question

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

1. 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...

2. 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, 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