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

  • 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


    You must be logged in to view attached files.
  • 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.

  • 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




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

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