Rank Operation Using SQL

By Unknown - December 27, 2014

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