0

How to fill [Order] column by order number in SQL Server 2000?

For example, I have a SQL:

select Id, Tilte 
from Tbl 
order by Date

I need to write order number from this query to column [Order] of Tbl table.

How to do this?

Thanks a lot for the help!

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Dmitry
  • 14,306
  • 23
  • 105
  • 189

1 Answers1

8

You can use ROW_NUMBER:

WITH CTE AS
(
   SELECT Id, Title, [Order] 
   , OrderNumber = ROW_NUMBER() OVER (ORDER BY Date)
   FROM Tbl 
)
UPDATE CTE SET [Order] = CTE.OrderNumber;

Here's a fiddle: http://sqlfiddle.com/#!3/8831d/2/0

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Thanks! But is possible more simple syntax there? – Dmitry Oct 16 '12 at 09:41
  • 2
    @Altaveron: This is probably the simplest way to do – Joe G Joseph Oct 16 '12 at 09:50
  • 1
    @Altaveron: Once you're familiar with a [`CTE`](http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx) it's very easy and the `ROW_NUMBER` is self-explanatory, isn't it? – Tim Schmelter Oct 16 '12 at 09:51
  • Why not just "SELECT [Order] = ROW_NUMBER() OVER (ORDER BY Date) FROM Tbl"? It works too. Is it correct? – Dmitry Oct 16 '12 at 13:28
  • @Altaveron: I thought you wanted to update that value in the table(_"fill column"_). Otherwise you can also use it without a cte. Apart from that, a cte is just an easy way to avoid many sub queries and to keep the sql clean and readable. – Tim Schmelter Oct 16 '12 at 13:35
  • Yes, but the code above is "[Order] = ROW_NUMBER()". The data on the column was updated correctly. Thanks for your help again. – Dmitry Oct 16 '12 at 13:36
  • SQL Server 2000 returns: 'ROW_NUMBER' is not a recognized function name :( – Dmitry Oct 16 '12 at 13:42
  • `ROW_Number` (and `Common Table Expressions`) were new in SQL-Server 2005. That's why it's important to tag the approriate version in the first place. All the more when you use such an old version. You would need a temporary table to simulate it. Have [a look](http://stackoverflow.com/questions/4081753/sql-server-row-number-on-sql-server-2000). – Tim Schmelter Oct 16 '12 at 13:44
  • I think temp table is not mandatory. But we have to use a hard SQL. – Dmitry Oct 16 '12 at 13:57