This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Are row_number and partition by available in MySQL?
Answer is NO.
Microsoft SQL Server support row_number and partition by for finding the unique record from a set of duplicate records. We can also set the order of data.
MySQL doesn’t provide this feature directly. We can achieve this by using an inline variable for SELECT statements.
Below is a full demonstration:
I prepared a few scripts for sample data and stored procedure for finding the only unique records.
Below is a full demonstration:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/*Create sample database and table with sample data.*/ CREATE DATABASE `Employee`; CREATE TABLE Employee.EmpDuplicate ( EmpRemarkID INTEGER ,EmpID INTEGER ,EmpRemark VARCHAR(50) ); INSERT INTO Employee.EmpDuplicate VALUES (1,25,'abc'),(2,25,'abc'),(3,25,'abc') ,(4,26,'xyz'),(5,27,'pqr'),(6,27,'pqr') ,(7,27,'pqr'),(8,27,'pqr'),(9,28,'xvk') ,(10,28,'xvk'),(11,29,'pqr'),(12,29,'pqr') ,(13,29,'abc'),(14,30,'abc'); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
/*Sample Stored Procedure to find Unique record from the group of duplicate.*/ DELIMITER $$ CREATE PROCEDURE Employee.usp_GETUniqueEmployeeRecords() BEGIN SET @rn1:=1; SET @pk1:=''; SELECT EmpRemarkID ,EmpID ,EmpRemark FROM ( SELECT EmpRemarkID ,EmpID ,EmpRemark ,@rn1 := IF(@pk1=CONCAT(EmpID,EmpRemark), @rn1+1,1) AS rowNumber ,@pk1 := CONCAT(EmpID,EmpRemark) FROM ( SELECT EmpRemarkID ,EmpID ,EmpRemark FROM Employee.EmpDuplicate ) A ) B WHERE rowNumber=1; END$$ DELIMITER ; |
|
1 2 |
/*Execute this Stored Procedure.*/ CALL Employee.usp_GETUniqueEmployeeRecords() |
Results:
