How do I use ROW_NUMBER() in SQL?

Question

Hi,

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

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

Answer 2

Count of rows is simply:

SELECT COUNT(*) FROM Users

(no need to use ROW_NUMBER())

Answer 3

SELECT num, UserName FROM 
 (SELECT UserName, ROW_NUMBER() OVER(ORDER BY UserId) AS num
  From Users) AS numbered
WHERE UserName='Joe'

Answer 4

Hi Matt, though I agree with others that you could use count() to get the total number of rows, heres how you can use the row_count():

1. To get the total no of rows:

   with temp as (
       select row_number() over (order by id) as rownum
       from table_name 
   )
   select max(rownum) from temp

2. To get the row numbers where name is Matt

   with temp as (
       select name, row_number() over (order by id) as rownum
       from table_name
   )
   select rownum from temp where name like 'Matt'

You can further use min(rownum) or max(rownum) to get the first or last row for Matt respectively.

These were very simple implementations of row_number(). You can use it for more complex grouping. Check out my response on Advanced grouping without using a sub query

Answer 5

Taking your heading literal, here is a blog entry explaining the so called window functions (where row_number is one of them):

http://www.depesz.com/index.php/2009/01/21/waiting-for-84-window-functions/