numbering each row

I'm sure this has been asked a bunch of times, but I can't find samples that
apply to many groups and columns. I also found ROW_NUMBER() function for SQL
2005. Unfortunately, I have SQL 2000. I realize this should be done in the
display of data but I have to get it done in a table. I believe RANK is what
I need, but didn't get it working. It would return a 1 for each row. I've
also considered using temp tables and an ID column, but that involves a LOT
of temp tables.
I need to add a row numbering column to the below results.
It must be grouped on the s.OrderNo field.
How do I get that to work?
SELECT s.OrderNo, t.Title, t.Artist, t.Location,
t.SortKey, t.MP3Files, t.OrderNo AS Disc_ID
FROM ItemStock s INNER JOIN
ItemPackCompilations p ON s.OrderNo = p.PackOrderNo INNER JOIN
ItemTitles t ON p.IncludeOrderNo = t.OrderNo
WHERE (s.Packed = 'Comp')
ORDER BY s.OrderNo, t.Title
thanks!> I'm sure this has been asked a bunch of times, but I can't find samples
> that apply to many groups and columns. I also found ROW_NUMBER() function
> for SQL 2005. Unfortunately, I have SQL 2000.
http://www.aspfaq.com/2427

> I realize this should be done in the display of data but I have to get it
> done in a table.
Why? Does a business requirement somehow state that the data must be
displayed directly from a table? Why?

> I believe RANK is what I need, but didn't get it working.
Well, this should fail for the same reason ROW_NUMBER() won't work for you.
RANK is SQL 2005+.

> SELECT s.OrderNo, t.Title, t.Artist, t.Location,
> t.SortKey, t.MP3Files, t.OrderNo AS Disc_ID
> FROM ItemStock s INNER JOIN
> ItemPackCompilations p ON s.OrderNo = p.PackOrderNo INNER JOIN
> ItemTitles t ON p.IncludeOrderNo = t.OrderNo
> WHERE (s.Packed = 'Comp')
> ORDER BY s.OrderNo, t.Title
I don't know what this is. Please see http://www.aspfaq.com/5006sql