Instead of ROW_NUMBER ORDER BY

Question

In my last post (Below link), I've tried to use the ROW_NUMBER ORDER BY and finally got the required solution. See the following link:

Get Wages Yearly Increment Column Wise Using Sql

Now I am trying to use the following query in Sql server 2000, just for demonstration purpose. I know, ROW_NUMBER ORDER BY can't be used in it. And after some googling, tried to use the following for Sql server 2000: (1st query)

SELECT k.ID, k.[Name], m.Amt,
    (SELECT COUNT(*) FROM EmpIncrement l WHERE l.EmpID <= m.EmpID) as RowNum
    FROM EmpIncrement m
JOIN Employee k ON m.EmpID = k.ID 

And I got this output:

ID  Name    Amt     RowNum
1   John    2000    2
2   Jack    8000    4
1   John    1000    2
2   Jack    4000    4

Similarly when I use the following with ROW_NUMBER ORDER BY, then it shows different output: (2nd query)

SELECT k.ID, k.Name, m.Amt, 
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY DATEPART(yy,IncrementDate)) as RowNum
    FROM EmpIncrement m 
JOIN Employee k ON k.ID = m.EmpID

Output:

ID  Name    Amt     RowNum
1   John    1000    1
2   John    2000    2
1   Jack    4000    1
2   Jack    8000    2

So it's noticed that the grouping for the employee ids (RowNum) are different in both the queries where the output of the second query is correct. I would like to know the difference of both the queries output and if the 1st query is equivalent to ROW_NUMBER ORDER BY. Thanks.

Note: I didn't include the table structure and sample data here again. Never mind - You can see the earlier post for that.

Answer 1

To recreate the partition by EmpId, your subquery should have l.EmpId = m.Empid. You really need a unique column or set of columns to unique identify a row for this version to work properly. In an attempt based on the given data, if EmpId, Amt are a unique pair you can use and l.Amt < m.Amt. If you have a surrogateid on the table, that would be better instead of Amt.

select
    k.id
  , k.[Name]
  , m.Amt
  , ( select count(*)
      from EmpIncrement l
      where l.Empid = m.Empid
        and l.Amt <= m.Amt
  ) as RowNum
from EmpIncrement m
  inner join Employee k
    on m.Empid = k.id

If you have no set of columns to uniquely identify and order the rows, you can use a temporary table with an identity() column.

create table #temp (tmpid int identity(1,1) not null, id int, [Name] varchar(32), Amt int);
insert into #temp (id, [Name], Amt);
select
    k.id
  , k.[Name]
  , m.Amt
from EmpIncrement m
  inner join Employee k
    on m.Empid = k.id;

select 
    t.id
  , t.[Name]
  , t.Amt   
  , ( select count(*)
          from #Temp i
          where i.Empid = t.Empid
            and i.tmpId <= t.tmpId
      ) as RowNum
from #temp t