In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:
line-no title_id
----------- --------
1 BU1032
2 BU1111
3 BU2075
4 BU7832
5 MC2222
6 MC3021
7 MC3026
8 PC1035
9 PC8888
10 PC9999
11 PS1372
12 PS2091
13 PS2106
14 PS3333
15 PS7777
16 TC3218
17 TC4203
18 TC7777
Answer：
SQL2005版本：SELECT ROW_NUMBER() over(order by [title_id]) as [line-no], [title_id]
FROM [pubs].[dbo].[titles]
order by [title_id]
FROM [pubs].[dbo].[titles]
order by [title_id]
SQL2000版本：
declare @dt table(line_no int identity(1,1), title_id varchar(6))
insert into @dt(title_id)
select title_id from [pubs].[dbo].[titles] order by [title_id]
select * from @dt;
insert into @dt(title_id)
select title_id from [pubs].[dbo].[titles] order by [title_id]
select * from @dt;