2

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
Community
  • 1
  • 1
Arbaaz
  • 321
  • 3
  • 13
  • 32
  • Did you not ask this question earlier today and accept the answer? – Raj Nov 19 '12 at 09: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 Nov 19 '12 at 09:38

2 Answers2

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 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 – Arbaaz Nov 19 '12 at 09:32
  • Post actual data and repro. Most likely your comparison of values is flawed (eg. you have spaces at the end) – Remus Rusanu Nov 19 '12 at 09:39
  • 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 – Arbaaz Nov 19 '12 at 09:48
  • If you want the rows to be sorted, add an ORDER BY clause to your SELECT. There is no other way. – Remus Rusanu Nov 19 '12 at 09:50
  • 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? – Arbaaz Nov 19 '12 at 09:53
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"

armen
  • 1,253
  • 3
  • 23
  • 41