Not as sorry as I am... It is Monday... I got confused with another thread and then spewed this crap out.

-R!

>Ric,
>
>I'm sorry, but how does it answer Kevin's question and would it be simpler to use int Identity field for this?
>
>>Try Something like this:
>>

>>CREATE TABLE RowNumberTest(RowNumber INT NULL, Notes NVARCHAR(100))
>>GO
>>
>>DECLARE @CntRow INT
>>DECLARE @InsCount INT
>>SET @InsCount = 0 
>>SELECT @CntRow = ISNULL(MAX(RowNumber),0) FROM RowNumberTest
>>
>>SET NOCOUNT ON
>>WHILE @InsCount < 2000
>>BEGIN
>>INSERT INTO RowNumberTest SELECT @CntRow, 'Some Note'
>>SET @CntRow = @CntRow + 1
>>SET @InsCount = @InsCount + 1
>>END
>>

>>
>>
>>>>>>I'm trying to generate row numbers for each row returned in my query:
>>>>>>
>>>>>>

>>>>>>SELECT *, (SELECT COUNT(t2.TabId) 
>>>>>>			FROM rbpo.rb_Tabs t2
>>>>>>			WHERE t2.TabId = t1.TabId ) AS RowNum
>>>>>>	FROM rbpo.rb_Tabs t1
>>>>>>	WHERE ParentTabId = @SJTabId
>>>>>>	ORDER BY RowNum ASC
>>>>>>

>>>>>>
>>>>>>The RowNum is coming back all 1's
>>>>>
>>>>>Strange query.
>>>>>

>>>>>Select T1.*, count(T2.TabID) over (partition by T1.TabID) as T1Count, row_Number() over (order by T1.TabID) as RowNum 
>>>>>from  rbpo.rb_Tabs t1 LEFT join rbpo.rb_Tabs t2
>>>>>			on t1.TabId = t2.TabId 
>>>>>	WHERE T1.ParentTabId = @SJTabId
>>>>>	ORDER BY RowNum ASC

>>>>
>>>>I'm open to a better way. It's SQL 2000 so OVER won't work.
>>>
>>>Ok, try still then
>>>

>>>select T1.*, T2.RowNum from FROM rbpo.rb_Tabs t1
>>>inner join (select count(TabID) as RowNum, TabID from rbpo.rb_Tabs group by TabID) T2 on T1.TabID = T2.TabID 
>>>where T1.ParentTabId = @SJTabId order by T1.TabID, T2.RowNum ASC

>>>
>>>Is the intention to order within the TabID group?