CODEWITHSUNDEEP
sql
Kmd
Kmd

Reputation: 9

How to give the serial number if data is repeating

if My table has this values i need to generate seqno column

ClientId clinetLocation seqno
001        Abc           1
001        BBc           2
001        ccd           3
002        Abc           1
002        BBc           2
003        ccd           1

Upvotes: 0

Views: 199

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

One option would be counting the grouped rows with respect to those columns :

select count(1) over ( order by ClientId, ClientLocation ) as seqno, 
       ClientId, ClientLocation
  from tab
 group by ClientId, ClientLocation;

where ClientId & ClientLocation combination seems unique.

Rextester Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You are looking for the row_number() function:

select ClientId, clinetLocation,
       row_number() over (partition by ClientId order by clinetLocation) as seqnum
from t;

This is a standard function available in most databases.

Upvotes: 1

Related Questions