RowNumbering - 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)

RowNumbering

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-26 : 11:35:11

durgesh writes "How to do Row numbering in a select statement irrespective of the order by clause
eg.

ID Name AddressID RowNumber
1 A 2 1
2 B null 2
3 C 1 3
4 D 4 4

this is ok when i say order by ID
but if i say order by AddressID the RowNumber goes haywire
RowNumer is some subquery to calculate the Rownumber and is not a column of any table."

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-26 : 12:22:39

The order of data in a database is meaningless.

Oracle background?

I would when you reorg data in oracle if the rownumber (RID) goes with the row or it's changed...

anyway


USE Northwind
GO

CREATE TABLE myTable99([ID] int, [Name] char(1), AddressID int)
CREATE TABLE #myTemp99([ID] int, [Name] char(1), AddressID int, RowNumber int IDENTITY(1,1))
GO

INSERT INTO myTable99 ([ID], [Name], AddressID)
SELECT 1, 'A', 2    UNION ALL
SELECT 2, 'B', null UNION ALL
SELECT 3, 'C', 1    UNION ALL
SELECT 4, 'D', 4
GO

SELECT * FROM myTable99

INSERT INTO #myTemp99([ID], [Name], AddressID)
     SELECT [ID], [Name], AddressID
       FROM myTable99
   ORDER BY AddressId

SELECT * FROM #myTemp99 ORDER BY RowNumber
GO
 
DROP TABLE myTable99
DROP TABLE #myTemp99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources