|
Custom Search
| |
|
MySQL DENSE_RANK() function The DENSE_RANK() function allocates a rank (integer number) to each row within a group of a data set, without gaps, and the rank values could duplicate. This is in contrast with RANK() and ROW_NUMBER() function. Please note that DENSE_RANK() has become available since MySQL version 8.0. If you get syntax error when running the DENSE_RANK() function on this page, check your MySQL version (SELECT VERSION ()), most likely you're running an older version of MySQL. Basic syntax:
MySQL DENSE_RANK() function can be viewed as "rank within a group". So the best way to understand MySQL DENSE_RANK() function is to ask you three questions.
Practice #1: Suppose you teach students in two classes. After final exam, you need to rank students in each class by their marks in the exam. So the answers to the 3 questions are:
Let's create exam_result table to show you how to rank students in this scenario. -- Create table CREATE TABLE exam_result ( StudentName varchar(20) NOT NULL, ClassName varchar(20) NOT NULL, StudentMark tinyint NOT NULL ); Insert data to exam_result table. -- Add sample data insert into exam_result values('Anne','Class A',92), ('Gary','Class A',85), ('Tom','Class A',85), ('John','Class A',76), ('Jerry','Class B',95), ('Nick','Class B',91), ('Joe','Class B',88), ('Ted','Class B',88), ('Bob','Class B',82); Here is the DENSE_RANK() query to get students ranked by exam results in each class. SELECT StudentName, ClassName, StudentMark, DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as StudentRank FROM exam_result; Please note that when a student has the same mark as another student, they both receive the same rank value, but unlike RANK() function, DENSE_RANK() function assigns consecutive rank values. In Class A, both Gary and Tom are assigned the same rank value 2 as they have the same mark, and John receives rank value 3 which is consecutively incremented from 2.
9 records returned: Practice #2: Query below does not use PARTITION BY clause which literally treats all classes as one group, so it ranks all students based on their marks regardless of which class they belong to. SELECT StudentName, ClassName, StudentMark, DENSE_RANK() OVER (ORDER BY StudentMark DESC) as StudentRank FROM exam_result;
9 records returned: Practice #3: The query below ranks products by the number of units in stock in each product category. select p.ProductName, c.CategoryName, p.UnitsInStock, DENSE_RANK() OVER (PARTITION BY c.CategoryName ORDER BY p.UnitsInStock) as stock_rank from products as p join categories as c on p.CategoryID=c.CategoryID order by c.CategoryName, p.UnitsInStock, stock_rank;
77 records returned: Practice #4: This query ranks customers by the total sales amount within each order date. Note that the ORDER BY clause within the brackets of the DENSE_RANK() OVER is a calculated column by using SUM function and GROUP BY for aggregation. select b.CustomerID, b.OrderDate, sum(a.UnitPrice * (1 - a.Discount) * a.Quantity) as Sales, DENSE_RANK() OVER (PARTITION BY b.OrderDate ORDER BY sum(a.UnitPrice * (1 - a.Discount) * a.Quantity)) as sales_rank from order_details as a join orders as b on a.OrderID=b.OrderID group by b.CustomerID, b.OrderDate order by b.OrderDate;
823 records returned:
Happy Coding! Other tutorials in this category 1. Using String Functions, Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |