To much Serious - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

To much Serious

Author

Topic

hafizsohaib
Starting Member

3 Posts

Posted - 2003-01-17 : 12:03:25

i have a table TEMP with 100 rows and 3 columns and,
i want to Select all these rows with 1 extra column named
Serial Number.
the result set should be...

S# col1 col2 col3
1 a a a
2 b e t
3 c e a
4 d s t
5 e g h
. . . .
. . . .
. . . .

my problem is to add a Record Number or Serial Number with each row.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-01-17 : 12:09:16

You are in luck! We wrote an article on returning a row number in a query:

http://www.sqlteam.com/item.asp?ItemID=1491

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

hafizsohaib
Starting Member

3 Posts

Posted - 2003-01-18 : 02:27:25

Good Guys

its too much help ful.
is there any other without temp table

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-18 : 15:44:35

Several methods posted over past few months

nr technique
set rowcount 1
while exists (select * from tbl where MEMBER_KEY is null
update tbl
set MEMBER_KEY = (select coalesce(max(MEMBER_KEY),0) + 1 from tbl)
set rowcount 0

page47 technique
update s
set member_key = (
select count(*)
from sometable
where surname < s.surname )
from sometable s

robvolk technique
DECLARE @seq int
SELECT @seq=0
UPDATE table
SET @seq = SEQUENCE_ID = @seq+1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources