Query
select au_id,authors_name from Mytable1 order by au_id
Results
12 | Sidney Sheldon |
76 | David Taylor |
100 | Mathew Arnold |
127 | Agatha Christy |
140 | Keith Davis |
au_id is unique in this table, so it is easy to use a co-related sub-query to generate sequential numbers.
Query
SELECT (SELECT count(au_id) FROM Mytable1 AS x WHERE x.au_id<= y.au_id) AS Sequence, au_id,authors_name FROM Mytable1 AS y order by au_id
Results
1 | 12 | Sidney Sheldon |
2 | 76 | David Taylor |
3 | 100 | Mathew Arnold |
4 | 127 | Agatha Christy |
5 | 140 | Keith Davis |
Note: “au_id” is a unique column.
Example 2:
Generate unique sequence numbers for a table that has no unique column.
Let us consider the table shown below. For tables with no unique columns, it is easy to use the identity function to generate unique sequence numbers.
Use tempdb go Create table Mytable2 (au_id int, authors_name varchar(100)) Go insert into MyTable2 select 100,'Mathew Arnold' insert into MyTable2 select 140,'Keith Davis' insert into MyTable2 select 76,'David Taylor' insert into MyTable2 select 127,'Agatha Christy' insert into MyTable2 select 12,'Sidney Sheldon' insert into MyTable2 select 12,'Mcarthur' insert into MyTable2 select 76,'Alan Smiles' insert into MyTable2 select 100,'Kreisler' go
Query
select * from mytable2 order by au_id
Results
12 | Sidney Sheldon |
12 | Mcarthur |
76 | Alan Smiles |
76 | David Taylor |
100 | Mathew Arnold |
100 | Kreisler |
127 | Agatha Christy |
140 | Keith Davis |
Query
select identity(int,1,1) as Sequence, au_id,authors_name into #x from Mytable2 order by au_id go select * from #x go drop table #x go
Results
1 | 12 | Sidney Sheldon |
2 | 12 | Mcarthur |
3 | 76 | Alan Smiles |
4 | 76 | David Taylor |
5 | 100 | Mathew Arnold |
6 | 100 | Kreisler |
7 | 127 | Agatha Christy |
8 | 140 | Keith Davis |