Rank Operation Using SQL

Rank - Make ranking structure using SQL tool

               If we have collection of data and we want to make it an well structure by reduce duplication and make view based on ranking; we have a simple and easy option to do this operation.It is called Rank. For example we have data such like,

 Name       Sales

John           10

Jennifer      15

Stella          20

Sophia       40

Greg          50

Jeff            20

The general idea to display rank in SQL is to do a self-join, then list out the results in order, and finally do a count on the number of records that's listed ahead of (and including) the record of interest.

So, we coming back to discussion. we have to make this data in ranking based order.so lets start with the query,

SELECT a1.Name, a1.Sales, COUNT (a2.Sales) Sales_Rank 

FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;

If you have  confusion let me explain ,

Here we are split one table into two different tables called a1 and a2.  We need to show name ,sales and count of sales which means the rank.Also looks the where condition . Here first table a1's sales is must be less than or equal to a2's sales . 

Here is the Output

Output


Name   Sales   Sales_Rank
Greg      50          1
Sophia   40          2
Stella     20          3
Jeff        20          3
Jennifer 15          5
John      10          6


By Varun K.R

Ociuz Technologies