Need to populate row number - 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

SQL Server Development (2000)

Need to populate row number

Author

Topic

veparala
Starting Member

30 Posts

Posted - 2008-02-19 : 08:23:06

Hi

I have one table with the following values.

SNo Empid FName LName

453 John Lorry
830 Steve Irina
298 David Dis

SNo is empty in all rows. I need to pupulate Sno with row_number() function. Can anybody help me out?

Thanks
Venkat

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 08:25:28

ROW_NUMBER() function is available only in SQL Server 2005 and above.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-19 : 08:33:58

CREATE TABLE #temp
(EmpId INT,
FName VARCHAR(20),
LName VARCHAR(20)
)

INSERT INTO #temp
SELECT 100,'aaa','aaa'
UNION
SELECT 101,'bbb','bbb'
UNION
SELECT 102,'ccc','ccc'
UNION

SELECT 103,'ddd','ddd'
UNION
SELECT 104,'eee','eee'

SELECT EmpId,FName,LName,
(select count(*) from #temp where Empid <> t.EmpID and EmpID<t.EmpID) as SNo
FROM #temp t
ORDER BY EmpId

DROP TABLE #temp

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 08:38:52

quote:
Originally posted by jackv

CREATE TABLE #temp
(EmpId INT,
FName VARCHAR(20),
LName VARCHAR(20)
)

INSERT INTO #temp
SELECT 100,'aaa','aaa'
UNION
SELECT 101,'bbb','bbb'
UNION
SELECT 102,'ccc','ccc'
UNION

SELECT 103,'ddd','ddd'
UNION
SELECT 104,'eee','eee'

SELECT EmpId,FName,LName,
(select count(*) from #temp where Empid <> t.EmpID and EmpID<t.EmpID) as SNo
FROM #temp t
ORDER BY EmpId

DROP TABLE #temp

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Please note that this method can be awefully slow on table with large number of records.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 08:39:05

quote:
Originally posted by jackv

CREATE TABLE #temp
(EmpId INT,
FName VARCHAR(20),
LName VARCHAR(20)
)

INSERT INTO #temp
SELECT 100,'aaa','aaa'
UNION
SELECT 101,'bbb','bbb'
UNION
SELECT 102,'ccc','ccc'
UNION

SELECT 103,'ddd','ddd'
UNION
SELECT 104,'eee','eee'

SELECT EmpId,FName,LName,
(select count(*) +1 from #temp where Empid <> t.EmpID and EmpID<t.EmpID) as SNo
FROM #temp t
ORDER BY EmpId

DROP TABLE #temp

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

You probably need to add 1 else SNo will start with 0. Also i really think the first condition check is redundant (it is implied by second check)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 08:46:44

Check this excellent article for discussion of various other techniques:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 08:48:03

quote:
Originally posted by veparala

Hi

I have one table with the following values.

SNo Empid FName LName

453 John Lorry
830 Steve Irina
298 David Dis

SNo is empty in all rows. I need to pupulate Sno with row_number() function. Can anybody help me out?

Thanks
Venkat

Where do you want to show data with serial number?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources