How do I use ROW_NUMBER()?

This query:

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

will return all rows where the `UserName` is `'Joe'` UNLESS you have no `UserName='Joe'`

They will be listed in order of `UserID` and the `row_number` field will start with 1 and increment however many rows contain `UserName='Joe'`

If it does not work for you then your `WHERE` command has an issue OR there is no `UserID` in the table. Check spelling for both fields `UserID` and `UserName`.

Need to create virtual table by using `WITH table AS`, which is mention in given Query.

By using this virtual table, you can perform CRUD operation w.r.t `row_number`.

QUERY:

WITH table AS
-
(SELECT row_number() OVER(ORDER BY UserId) rn, * FROM Users)
-
SELECT * FROM table WHERE UserName='Joe'
-

You can use `INSERT`, `UPDATE` or `DELETE` in last sentence by in spite of `SELECT`.

You can use this for get first record where has clause

SELECT TOP(1) * , ROW_NUMBER() OVER(ORDER BY UserId) AS rownum
FROM Users
WHERE UserName = 'Joe'
ORDER BY rownum ASC

`ROW_NUMBER()` returns a unique number for each row starting with 1. You can easily use this by simply writing:

ROW_NUMBER() OVER (ORDER BY 'Column_Name' DESC) as ROW_NUMBER