SQL Row_Number() function to add a row number with groupings

I have a query that returns the below data less the Row_Number column. The query is grouped by PayeeId, PaidDate, SerialNumber, and Id. I would like to add a column that would display the row numbers that appear in the below example's Row_Number column. As you can see, it is not incrementing the row number if the row is part of a group.

Id  PayeeId  PaidDate         Serial    AcctNumber    Name    Row_Number
1   100          6/30/2014       A123      84053              Kim A             1
2   101          8/1/2014         A555      79947              Alex S             2
3   101          8/1/2014         A555      74518              Larry K           2
4   102          8/15/2014       A249      83386              Ralph S          3
5   103          9/3/2014         A598      98608              Leslie D         4
6   103          9/3/2014         A598      79202              William J        4
7   103          9/3/2014         A598      98921              Billy B            4
8   104          10/17/2014     A179      98913              Jack L             5
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You need a sub-query or CTE. Otherwise ROW_NUMBER() is threated as a window function. E.g.

  ( yourSelectWithGroupBy )
  FROM Data;

Open in new window

Scott PletcherSenior DBACommented:
I believe this will do it, but don't have any useable data to test it:

SELECT Id, PayeeId, PaidDate, Serial, AcctNumber, Name,
    DENSE_RANK() OVER(ORDER BY PayeeId) AS [Row_Number]
FROM ...

Edit: Looks OK to me...:

SELECT Id, PayeeId, PaidDate, Serial, AcctNumber, Name,
    DENSE_RANK() OVER(ORDER BY PayeeId) AS [Row_Number]
          SELECT  1 id, 100 payeeid, '6/30/2014' paiddate, 'A123' serial, '84053' acctnumber, 'Kim A' name, 1 as row_num
          UNION ALL SELECT  2,   101,          '8/1/2014',         'A555',      '79947',              'Alex S',             2
UNION ALL SELECT  3,   101,          '8/1/2014',         'A555',     '74518',              'Larry K',           2
UNION ALL SELECT  4,   102,          '8/15/2014',       'A249',      '83386',              'Ralph S' ,         3
UNION ALL SELECT  5,   103,          '9/3/2014'  ,      'A598' ,     '98608' ,             'Leslie D' ,        4
UNION ALL SELECT  6,   103,          '9/3/2014'   ,      'A598' ,    '79202'  ,            'William J' ,       4
UNION ALL SELECT  7,   103,          '9/3/2014'    ,     'A598'  ,   '98921'   ,           'Billy B'    ,        4
UNION ALL SELECT  8,   104,          '10/17/2014'   ,  'A179'     ,  '98913'    ,          'Jack L'      ,       5    
) AS test_data

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
BINGO. Thanks Scott, that's exactly what I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.