optimisteve optimisteve - 2 months ago 6
SQL Question

Want data generated incrementally, random numbers generated instead

Someone here was kind enough to provide a script to help me generate numbers in increment so it could look like the PIN number column below. I ran the script and noticed the minimum PIN number generated is 10000 which really should be 4310

Table name: Citizen

Firstname Lastname PIN Address Age other columns....
John Smith 4310 20001 19
Andrew Evans 4311 363252 30
Bill Towny 4312 63322 67
Dame Beaut 4313 34222 34



WITH PIN_NO
AS
(
SELECT 4310 + ROW_NUMBER() OVER(ORDER BY [All LP First Name]) NEW_PIN, *
FROM [dbo].[C3MDMRevsBens]
)

UPDATE PIN_NO
SET [Account Reference Number] = CAST(NEW_PIN AS VARCHAR(10))


When I ordered the PIN number in asc order, below are the numbers generated starting from record 1 .....

select * from Citizen order by PIN asc


OR

SELECT MIN(PIN) FROM Citizen


.

10000
100000
100001
100002
100003
100004
100005
100006
100007
100008
100009
10001


Thanks

Answer

your query works fine... I've tested. As @bassrek says it's your select query that has to be reviewed:

select * from Citizen order by CAST(Pin AS int)

Because Pin seems to be a nvarchar and 10000 comes before 4.

So, you have to change pin column to int or use a cast in select.

Comments