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:
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment