How to add unique row numbers if there is a duplicate records in sql server

  • bsushu

    SSC Enthusiast

    Points: 112

    Hi All,

    I am looking for a way to add unique sequence number to my result set in sql server  even though there is duplicates or not.

    For Example, If first 3 rows are duplicate rows then the sequence should be 1 instead of 1,2,3. Then if the 4th,5th row is duplicate then sequence should be 2 instead of 4,5, If the 6th row is not duplicate then it should be 3 like that

    Please find the attached image

     

    Attachments:
    You must be logged in to view attached files.
  • anthony.green

    SSC Guru

    Points: 112351

    You would want to look at the DENSE_RANK() window function for this, ordered by the customer name.

     

    If you still struggle, let us know, with what you tried and we can point you in the right areas where it seems to be going wrong.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    I normally use ROWNUMBER() over (order by x partition by y) - usually because It involves finding the first order of the day for that product type (partition by product order by dateordered)

    but any of the windowing functions could help - just make sure you understand the difference between rank, dense rank, row number

    shout out if you need anything

     

     

    MVDBA

  • sunita2912

    SSC Veteran

    Points: 278

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply