RSS

sequence number Add in sql server Query

26 Jun

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
Advertisements
 
Leave a comment

Posted by on June 26, 2010 in SQL Query

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: