SQL Server : CROSS APPLY equivalent for ROW_NUMBER() query

Question

I'm trying to find a different way to write a ROW_NUMBER() query using CROSS APPLY so I can compare the performance.

In the below simple example, an employee table is created, some test data is inserted and a SELECT with a ROW_NUMBER() window function is used to find the employee in each department with the longest tenure.

Is there another way to write the SELECT using a CROSS APPLY?

CREATE TABLE [dbo].[tblEmployee]
(
    [ID] [INT] NOT NULL,
    [DeptID] [TINYINT] NOT NULL,
    [EmpName] [VARCHAR](100) NOT NULL,
    [Tenure] [TINYINT] NOT NULL,

    CONSTRAINT [PK_tblEmployee] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.tblEmployee (ID, DeptID, EmpName, Tenure) 
VALUES ('1', '1', 'John', 2),
       ('2', '1', 'Mary', 5),
       ('3', '2', 'Joe', 3),
       ('4', '3', 'Bill', 10),
       ('5', '2', 'Marilynn', 9),
       ('6', '3', 'Sue', 7);

SELECT 
    EmpName, DeptID, Tenure 
FROM  
    (SELECT 
         EmpName, DeptID, Tenure, 
         ROW_NUMBER() OVER(PARTITION BY DeptID ORDER BY Tenure DESC) TenureRank
     FROM 
         tblEmployee) e 
WHERE 
    e.TenureRank = 1
ORDER BY 
    DeptID

EDIT: I would prefer to not use a CTE as part of the SELECT

Answer 1

The cross apply equivalent would be:

select e.*, a.seqnum
from tblEmployee e cross apply
     (select count(*) as seqnum
      from tblEmployee e2
      where e2.deptid = e.deptid and
            (e2.tenure > e.tenure or
             e2.tenure = e.tenure and e2.id >= e.id
            )
     ) a;

You would not want to do this, because it is much, much less efficient, than row_number(). Note the use of id for the comparison to ensure unique numbers.