title: SQL Server : CROSS APPLY equivalent for ROW_NUMBER() query
body: <p>I'm trying to find a different way to write a <code>ROW_NUMBER()</code> query using <code>CROSS APPLY</code> so I can compare the performance. </p>
  
  <p>In the below simple example, an employee table is created, some test data is inserted and a <code>SELECT</code> with a <code>ROW_NUMBER()</code> window function is used to find the employee in each department with the longest tenure.</p>
  
  <p>Is there another way to write the <code>SELECT</code> using a <code>CROSS APPLY</code>?</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT</strong>: I would prefer to not use a CTE as part of the <code>SELECT</code></p>