Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: Partition: Multiple Columns

    Hello, Can we use Partition with Multiple Columns
    I have the data like below:
    Policy SubPolicy AgentCode Percentage GEN_ID (primary key)
    1 1 1001 5o% 1
    1 1 1009 5o% 2
    1 2 1001 100% 3
    1 3 1001 100% 4
    2 1 1001 100% 5
    2 2 1001 5o% 6
    2 3 1001 33% 7
    2 3 1009 33% 8
    2 3 1245 33% 9
    3 1 1001 100% 10
    3 2 1245 100% 11

    Result: Can we use Row_number() like below:
    Policy SubPolicy AgentCode Percentage Row_Number()
    1 1 1001 5o% 1
    1 1 1009 5o% 1
    1 2 1001 100% 2
    1 3 1001 100% 3
    2 1 1001 100% 1
    2 2 1001 5o% 2
    2 3 1001 33% 3
    2 3 1009 33% 3
    2 3 1245 33% 3
    3 1 1001 100% 1
    3 2 1245 100% 2

    I want to auto number in each Policy when Subpolicy changes, but when there are multiple SubPolicy in that policy (because multiple agent share the same SubPolicy), they will have the same Row number

    When Policy changes, the Row number will restart.

    I try to use:
    ROW_NUMBER() over(partition by Policy, AgentCode order by GEN_ID) as Row

    But it's not correct.

    Pls give me a hand. Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You probably want to use RANK(), not ROW_NUMBER()
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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