CODEWITHSUNDEEP
sqlsql-server
Matt
Matt

Reputation: 4190

Create unique ID based on grouped UserId

trying to write some SQL that does the following.

If I have a user id field, 12345, and there are 10 records for that user, I want to make a field that goes 1234xxxx where xxxx refers to order of those records, based on a date field, 1 - 10

so 12340001, 12340002, 12340003 etc, up to 12340010

Thoughts?

Upvotes: 1

Views: 65

Answers (2)

mohan111
mohan111

Reputation: 8865

 Declare @x Table (Id INT)

 INSERT INTO @x (ID) VALUES (1001),(1002),(1003),(1004),(1005)
 select * from @x

 select (
 left(ID, 4)+right(10000 +DENSE_RANK() over ( order by Id), 4)
       )
       from @x

we can also use dense rank to get desired output

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Here is a method for getting the new value, assuming the userid is a string

select (left(userid, 4)+right(100000 +
                              row_number() over (partition by userid order by datefield), 4)
       )

You can also use this in an update statement, if you want to change the value in the table.

Upvotes: 3

Related Questions