CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 1569

Row number generating for repeated clients - SQL

I have a simple row_number based query that is not producing me the desired result:

create table #fake(client_id int)
insert #fake
values(1991) ,(1991),(1992)

select distinct
    _row_ord = 100,
    _client_id =  c.client_id,
    _segment = 'fake',
    --_row = row_number() over(order by c.client_id)
    _value = 
        concat
                (
                'HL*',
                row_number() over(order by c.client_id) + 1 ,'*',
                'testfield','~' 
                )
from #fake c 

drop table #fake

So my current output is:

enter image description here

Desired output:

enter image description here

I am realizing that my row_number query is missing something. Any help?

Upvotes: 1

Views: 38

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

change row_number() to dense_rank() to get the desired results:

select distinct
    _row_ord = 100,
    _client_id =  c.client_id,
    _segment = 'fake',
    --_row = row_number() over(order by c.client_id)
    _value = 
        concat
                (
                'HL*',
                dense_rank() over(order by c.client_id) + 1 ,'*',
                'testfield','~' 
                )

Results:

_row_ord    _client_id  _segment    _value
100         1991        fake        HL*2*testfield~
100         1992        fake        HL*3*testfield~

See a live demo on rextester.

Upvotes: 3

Related Questions