Ranking without ranking functions

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?

---

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 don't 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 

---

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?

---

---Script for the Ranking salesperson without Rank function

select salesperson_id ,amt , 
  (select count(*) + 1 from 
       (select sum(sales_amount)as Amt 
              from sales group by salesperson_id order by sum(sales_amount) desc )B
        where A.amt < b.amt ) as Rank
 from 
 (select salesperson_id,sum(sales_amount)as Amt from sales group by salesperson_id order by sum(sales_amount) desc ) A
 order by amt desc;