Sql Server 2000 Puzzle

Question

 SELECT    Id ,
           ActionDate ,
           Operation ,
           Date ,
           [Payroll Number]    
FROM 
[dbo].[tblx]

1   2018-01-10 00:00:00.000 NEW     2018-01-30 00:00:00 X0154X
2   2018-01-10 00:00:00.000 NEW     2018-01-31 00:00:00 X0154X
3   2018-01-10 00:00:00.000 NEW     2018-02-01 00:00:00 X0154X
4   2018-01-10 00:00:00.000 DELETE  2018-02-01 00:00:00 X0154X
5   2018-01-10 00:00:00.000 NEW     2018-02-02 00:00:00 X0154X
6   2018-01-10 00:00:00.000 NEW     2018-02-11 00:00:00 X0154X

I need the Latest record based ON Action Date for a Payroll/Date combination. So I tried this on SQL server 2000. The problem, however, is that ROW_NUMBER() wasn't introduced until SQL Server 2008.

This gives me 4 INSTEAD OF 5 records. Record with ID 4 is not returned as there is tie with the Action Date.

SELECT     Id ,
           ActionDate ,
           Operation ,
           Date ,
           [Payroll Number]
  FROM (SELECT *, (SELECT COUNT(*)
                   FROM dbo.tblx AS counter
                   WHERE counter.[Payroll Number] = dbo.tblx.[Payroll Number]
                    AND counter.[Date] = dbo.tblx.[Date]
                    AND  counter.ActionDate >= dbo.tblx.ActionDate) AS rn
        FROM dbo.tblx
        WHERE Status IN ( 0, 5 )
          AND Category = 'Holiday') AS r1
  WHERE r1.rn = 1

  Id  ActionDate                Operation   Date                Payroll Number
  1   2018-01-10 00:00:00.000   NEW         2018-01-30 00:00:00 X0154X
  2   2018-01-10 00:00:00.000   NEW         2018-01-31 00:00:00 X0154X
  5   2018-01-10 00:00:00.000   NEW         2018-02-02 00:00:00 X0154X
  6   2018-01-10 00:00:00.000   NEW         2018-02-11 00:00:00 X0154X

Is there a way to get this without ROW_NUMBER() on SQL Server 2000?

Answer 1

In the pre-window functions days, you could do:

SELECT x.* 
FROM [dbo].[tblx] x
WHERE x.ActionDate = (SELECT MAX(x2.ActionDate)
                      FROM [dbo].[tblx] x2
                      WHERE x2.[Payroll Number] = x.[Payroll Number] AND
                            x2.Date = x.Date
                     );

This syntax is used in databases such as MySQL, SQLite, and MS Access that do not support window functions.

If you can define "latest" by id, then use that instead of date in the subquery:

SELECT x.* 
FROM [dbo].[tblx] x
WHERE x.id = (SELECT MAX(x2.id)
              FROM [dbo].[tblx] x2
              WHERE x2.[Payroll Number] = x.[Payroll Number] AND
                    x2.Date = x.Date
             );