How to generate random number using sql? [duplicate]

Question

Possible Duplicate:
How do I generate random number for each row in a TSQL Select?

I am importing excel sheet into sql database, db table has the following columns: srno | passportnumber|flightnumber

now when i import excel sheet the srno stays NULL after importing. Now i need this Srno column to be updated using sql query such that it has a random number or non-random serial number but it should have same srno value where the passport number is same i mean: If passportnumber abc123 occurs 4 times each time the srno must have same value for eg :

srno | passportnumber|flightnumber
12   | ab32434       |AI-2011
13   | ab565235      |AI-2562
14   | ABC123        |AI-2025
14   | ABC123        |AI-2000
14   | ABC123        |AI-5623
15   |XYZ12334       |AI-5625

Did you not ask this question earlier today and accept the answer? — Raj
– Raj, Commented Nov 19, 2012 at 9:22
I asked a similar question but not exactly the same question, i accepted the answer but then i realized its not working. I unselected the answer. — Arbaaz
– Arbaaz, Commented Nov 19, 2012 at 9:38

Remus Rusanu · Accepted Answer · 2012-11-19 09:21:28Z

2

with cte as (
  select DENSE_RANK() OVER (ORDER BY passportnumber) as rank,
    srno
  from table)
update cte
  set srno = rank;

This will give serial numbers in order of passport. Modifying this to give truly random numbers based on a lookup dictionary preloaded with the randoms is trivial and I leave it as an exercise.

answered Nov 19, 2012 at 9:21

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arbaaz Over a year ago

there is a strange problem with your code code, when i select * from table1 the srno column shows serial no, each one distinct i mean 1,23,4,5,6,7,8.. so on even if the passport is same ie.. for passport abc123 srno. is 8 and then for another occurrence of abc123 the srno.is 9 and then 10. But when use select from * table1 order by passport, i get perfect desired results.. just like the one i wanted. Same srno for every occurrence of same passportnumber

Remus Rusanu Over a year ago

Post actual data and repro. Most likely your comparison of values is flawed (eg. you have spaces at the end)

Arbaaz Over a year ago

I just noticed that your code is working perfect, but can you please tell me that when i use "select * from table1" how do the rows get sorted? order by which column? Because my first column in the table is Srno and the values are like : 1 2 3 1 2 3 while i was expecting it to be displayed like: 1 1 2 2 3 3

Remus Rusanu Over a year ago

If you want the rows to be sorted, add an ORDER BY clause to your SELECT. There is no other way.

Arbaaz Over a year ago

Can i permanently order the rows using some query? so that next time i use select * from table1 i do not need to use order by clause?

armen · Accepted Answer · 2012-11-19 09:37:54Z

0

If same passport number occurs more than once, than first you should consider normalizing your data creating a table that contain the unique Passport numbers plus an ID column with Identity Increment ON.

Then update the Flights table with Passports ID (in your case "srno"), and remove the Column "passportnumber"

answered Nov 19, 2012 at 9:37

armen

1,2754 gold badges23 silver badges43 bronze badges

1 Comment

Arbaaz Over a year ago

I have to do it in the same table.

Collectives™ on Stack Overflow

How to generate random number using sql? [duplicate]

2 Answers 2

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Linked

Related