Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Row Number - Grouped

    Hi

    select usergroup,USERNAME
    from tmsuser.USERGRP group by USERGROUP,USERNAME order by Usergroup

    Returns

    usergroup USERNAME
    003 CIARA
    004 CIARA
    004 MAURA
    006 CIARA
    008 CIARA
    008 DELWEN
    008 JAMESD
    008 LAURAP
    008 MAURA
    008 SINEADON
    009 CIARA
    009 JOET


    What I would like to do is return a row number based on a grouping of usergroup.

    ie
    RowNo usergroup USERNAME
    1 003 CIARA
    1 004 CIARA
    2 004 MAURA
    1 006 CIARA
    1 008 CIARA
    2 008 DELWEN
    3 008 JAMESD
    4 008 LAURAP
    5 008 MAURA
    6 008 SINEADON
    1 009 CIARA
    2 009 JOET


    Is this possible?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Check out the Row_Number() function in the manual (I think the section is called The OVER Clause).

    But a vague stab in the dark would look like:
    Code:
    Row_Number() OVER (PARTITION BY usergroup ORDER BY username)
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks George,

    Spot on - I had just found the partition section

    Below worked

    Select ROW_NUMBER() OVER(PARTITION BY [USERGROUP]
    ORDER BY [Usergroup]) ,USERGROUP,USERNAME as s_index from tmsuser.USERGRP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •