Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Use Access SQL to do a grouped ranking

Tags:

sql

ms-access

How do I rank salespeople by # customers grouped by department (with ties included)?

For example, given this table, I want to create the Rank column on the right. How should I do this in Access?

SalesPerson Dept #Customers Rank
Bill        DeptA     20    1
Ted         DeptA     30    2
Jane        DeptA     40    3
Bill        DeptB     50    1
Mary        DeptB     60    2

I already know how to do a simple ranking with this SQL code. But I don't know how to rework this to accept grouping.

Select Count(*) from [Tbl] Where [#Customers] <  [Tblx]![#Customers] )+1

Also, there's plenty of answers for this using SQL Server's Rank() function, but I need to do this in Access. Suggestions, please?

like image 845
PowerUser Avatar asked Dec 16 '10 16:12

PowerUser


2 Answers

SELECT *, (select count(*) from tbl as tbl2 where
tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl

Just add the dept field to the subquery...

like image 134
Paul Abbott Avatar answered Sep 24 '22 07:09

Paul Abbott


Great solution with subquery! Except for huge recordsets, the subquery solution gets very slow. Its better(quicker) to use a Self JOIN, look at the folowing solution: self join

SELECT tbl1.SalesPerson , count(*) AS Rank 
FROM tbl AS tbl1 INNER JOIN tbl AS tbl2 ON tbl1.DEPT = tbl2.DEPT 
    AND tbl1.#Customers < tbl2.#Customers 
GROUP BY tbl1.SalesPerson 
like image 23
Bas Volkers Avatar answered Sep 23 '22 07:09

Bas Volkers