0

I have in my table 3 string column and I want to create unique column with a row_nubmer. I have tried something like this but didn't get unique values:

ROW_NUMBER() OVER(PARTITION BY ([Marker1] + [Marker2] + [Marker3])
     ORDER BY ([Marker1] + [Marker2] + [Marker3])  DESC) ROWNUMBER
teh_sql
  • 1
  • remove the `partition by` clause – LoztInSpace Sep 06 '21 at 07:59
  • But still didn't get unique values for all 3 columns. Now I have rownuber long as table is.. I need that one rownumber is for the same combination of ([Marker1] + [Marker2] + [Marker3]) values. – teh_sql Sep 06 '21 at 08:20

1 Answers1

1

Use the DENSE RANK function:

DENSE_RANK() OVER (ORDER BY [Marker1], [Marker2], [Marker3]) AS ROWNUMBER

Note that you do not need to concatenate strings to identify unique values. This can lead to false positives.

A   B   A+B
-----------
XX  X   XXX
X   XX  XXX
id'7238
  • 2,428
  • 1
  • 3
  • 11