How do I use ROW NUMBER

Question

Can I use the syntax ROW_NUMBER() to get the following​

SELECT max(ROW_NUMBER() OVER(ORDER BY CustomerId)) FROM Customers

but it didn't work

1. 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 CustomerId) From Users WHERE CustomerName='John'

but this also did not work

Answer 1

You can use the query as:

SELECT COUNT(*) FROM Users 

to get the count.

The primary key of the row should be used to identify a particular row. 

---

If you wanted to return Row_Number() in your main query,

SELECT ROW_NUMBER() OVER (Order by Customer_Id) AS RowNumber, Field1, Field2, Field3
FROM Users

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

SELECT users.Customer_Id
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Customer_id) AS Row, Customer_Id
     FROM Users ) users 
WHERE Row = CurrentRow - 4