Ranking without ranking functions

Question

Hi, I have written the following query so as to select the rank of each customer and show it with other information in the output.

use northwind
go

select 
Employees.EmployeeID as ID, 
FirstName+' '+LastName as Name,
DENSE_RANK() over (order by SUM(Orders.OrderID)) as [Rank]
from 
employees 
inner join 
orders 
on
Employees.EmployeeID = Orders.EmployeeID
group by 
Employees.EmployeeID, 
FirstName+' '+LastName

But I want to know how can I do the ranking job without using DENSE_RANK() function. Is it possible?

Answer 1

Yes, simply count the number of rows with (sort column) value less than the current rows sort column value...

   Select *, 
         (Select Count(*) From Table 
          Where SortColumn <= t.SortColumn) as Rank
   From table t

NOTE: The sort column should be unique if you donlt want to count duplicates. For example, if you want to rank test scores, then this technique will give everyone with the same score the same rank, instead of randomly assigning them all different ranks).

in yr example,

Select e.EmployeeID as ID,   
   FirstName+' '+LastName as Name,  
   (Select Count(*) From Employees
    Where EmployeeID <= e.EmployeeId)  
From employees e  
   Join Orders o  
      On e.EmployeeID = o.EmployeeID  
Group by e.EmployeeID, FirstName+' '+LastName 

Answer 2

Without using dense_rank you basically have a version of the running totals problem.

This is something that is difficult to do in SQL in an efficient way. You can use a triangular join as in Charles's answer. If you have more than a few hundred records you will find that this is out performed by a cursor however.

Why don't you want to use dense_rank?