TOP, LIMIT, ROWNUM vs DENSE_RANK

What would you do if you were asked to identify top-ten products based on their prices?

In SQL Server, using a TOP clause with a specified number of records with descending order of price?

In MySQL and Impala, using a LIMIT clause with a specified number of records with descending order of price?

Basically, TOP (in SQL Server), LIMIT (in MySQL and Impala), or ROWNUM (in Oracle SQL*Plus) keywords are used for pagination or page-results or limit the number of rows and is useful when applied on large tables. They will not help in identifying the rankings directly unless some workarounds. 
Let’s create some sample data and do some exercises to understand the scenario.

The following statement will create a “Products” table:

CREATE TABLE Products
(
ProductName STRING,
Price DECIMAL(7,2)
);

product_table_creation

INSERT INTO Products (ProductName, Price) VALUES
(‘Delights breads’,25),
(‘Galaxy Chocolates’,20),
(‘Kitkat Chocolates’,22),
(‘Rainbow Chocolates’,19),
(‘Americana Chocobread’,26),
(‘Palm Milky Chocobars’,28),
(‘Bounty chocolates’,26),
(Sparkles chocos’,23),
(‘Smiley Cocos’,21),
(‘DelightPlus chocos’,22),
(‘Softy chocobar’,18),
(‘Minis chocos’,8)
 
Now we have “Products” table with data.
 
product_data
 
Let’s query against the table to retrieve “Products” data based on the descending order of “Price” 
 
SELECT ProductName, Price FROM Products
ORDER BY Price DESC;

product_orderbyprice

Now, let us retrieve the top ten product information based on the highest price using LIMIT clause.

SELECT ProductName, Price FROM Products
ORDER BY Price DESC
LIMIT 10;

product_orderbyprice_limit10

This returned ten rows however by looking at the data we can say it is not giving the information what we are looking for. i.e. the top-ten product information. There are some products that have the same price hence it will be considered only Top-8 products.

In this scenario, we need to use DENSE_RANK to fetch the ranking of the products based on their price.

SELECT * FROM (
SELECT ProductName, Price, DENSE_RANK() OVER (ORDER BY Price DESC)
AS RankValue FROM Products)
AS Tab
WHERE RankValue <= 10;

product_denserank
 
Finally, we have successfully retrieved top-ten product information.

 

Hope you find this article helpful.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s