|
Custom Search
| |
|
MySQL ROW_NUMBER() function The ROW_NUMBER() function allocates a different row number (integer number) to each row within a group of a data set, without both gaps and duplicated values. This is in contrast with RANK() and DENSE_RANK function. Please note that ROW_NUMBER() has become available since MySQL version 8.0. If you get syntax error when running the ROW_NUMBER() function on this page, check your MySQL version (SELECT VERSION ()), most likely you're running an older version of MySQL. Basic syntax:
MySQL ROW_NUMBER() function can be viewed as "numbering within a group". So the best way to understand MySQL ROW_NUMBER() function is to ask you three questions.
Practice #1: Suppose you teach students in two classes. After final exam, you need to assign a unique number to each student 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 assign unique numbers to 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 ROW_NUMBER() query to get students uniquely numbered by exam results in each class. SELECT StudentName, ClassName, StudentMark, ROW_NUMBER() 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, each of them receives a distinct row number. Furthermore, unlike RANK() and DENSE_RANK(), ROW_NUMBER() function assigns consecutive, non-duplicated numbers. In Class A, both Gary and Tom are assigned different numbers even though they have the same mark, and John receives number 4 which is consecutively incremented from 3.
9 records returned: Practice #2: Query below does not use PARTITION BY clause which literally treats all classes as one group, so it assigns a unique and incremented number to each student based on their marks regardless of which class they belong to. SELECT StudentName, ClassName, StudentMark, ROW_NUMBER() OVER (ORDER BY StudentMark DESC) as StudentRank FROM exam_result;
9 records returned: Practice #3: The query below assigns unique numbers to products by the number of units in stock in each product category. select p.ProductName, c.CategoryName, p.UnitsInStock, ROW_NUMBER() 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 numbers customers by the total sales amount within each order date. Note that the ORDER BY clause within the brackets of the ROW_NUMBER() 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, ROW_NUMBER() 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 |