Ranking Queries in MS Access SQL

SMAN 15 Bandar Lampung
0
Introduction
A query to rank or number the results is often requested. In more powerful database management systems such as Microsoft SQL and DB2, there are often functions to do this. However, in Microsoft Access, no such function exists.

Options
In Access, there are several workarounds:
  1. A running sum in a report
  2. VBA code called in the SELECT clause
  3. A subquery in the SELECT clause
  4. A DCount in the SELECT clause
  5. Joining the table to itself and using a COUNT

Option 1 is probably the easiest if you want to number the rows consecutively but is the least flexible of the options.

Options 2, 3, and 4 require each row to be evaluated separately and can be slow for large data sets.

Option 5 is the most complicated to understand but can often be the most efficient. That is the option I will be discussing in this article.

Examples
Given the following table and data:
Expand|Select|Wrap|Line Numbers
  1. ID Salesperson Division    NumberSold
  2. 1  Robert      Electronics 99
  3. 2  Jenny       Electronics 54
  4. 3  Billy       Appliances  54
  5. 4  Karen       Appliances  102
  6. 5  Kim         Appliances  30
For the first example, let's say you want to rank all the salespeople by number of items sold, you can join the table to itself on the number sold and do a count.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) + 1 AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.NumberSold < t2.NumberSold
  11. GROUP BY
  12.    t1.Salesperson,
  13.    t1.Division,
  14.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         2
  3. Jenny       Electronics 54         3
  4. Billy       Appliances  54         3
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         5
Note that this gives ties the same rank. If what you want is to number the rows rather than rank them, you will need to use a unique field.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.NumberSold < t2.NumberSold OR
  11.          (t1.NumberSold = t2.NumberSold AND
  12.          t1.ID <= t2.ID)
  13. GROUP BY
  14.    t1.Salesperson,
  15.    t1.Division,
  16.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         2
  3. Jenny       Electronics 54         4
  4. Billy       Appliances  54         3
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         5
If you want to break out the rankings or numbering by grouping field(s), you can do that by including them in the JOIN clause.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.Division = t2.Division AND
  11.          t1.NumberSold <= t2.NumberSold
  12. GROUP BY
  13.    t1.Salesperson,
  14.    t1.Division,
  15.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         1
  3. Jenny       Electronics 54         2
  4. Billy       Appliances  54         2
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         3
Note that this ranks from highest to lowest. Going from lowest to highest merely requires flipping the less than operator to a greater than operator. 
sumber : https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql
Tags

Posting Komentar

0Komentar
Posting Komentar (0)