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.

SqlZim · Accepted Answer · 2017-05-26 18:18:39Z

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

edited May 26, 2017 at 18:18

answered May 26, 2017 at 16:54

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AT-2017 Over a year ago

I guess, with the word Generic, you are asking about unique id in the table and if so, it's included. Thanks for the answer @SqlZim.

SqlZim Over a year ago

@AT-2017 I should have used the word surrogate, I'll correct that.

AT-2017 Over a year ago

It worked and I just slightly changed this section where l.Empid = m.Empid and l.Amt <= m.Amt to this where l.Empid = m.Empid and l.IncrementID >= m.IncrementID (In the 1st query) as I've unique id in the EmpIncrement table. Thanks a lot @SqlZim.

Collectives™ on Stack Overflow

Instead of ROW_NUMBER ORDER BY

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related