Bagaimana cara mendapatkan rownum di mysql?

Saat menggunakan MySQL, banyak pengguna perlu mendapatkan nomor baris dari suatu record atau melakukan beberapa operasi berdasarkan nomor baris. Hingga MySQL 8. 0 dirilis, ini dulunya sangat membosankan karena Anda perlu mengurutkan tabel dan menetapkan nomor baris ke variabel sementara. Sejak versi 8. 0, MySQL mendukung fungsi ROW_NUMBER() yang secara langsung mengembalikan nomor baris untuk Anda. Pada artikel ini, kita akan melihat fungsi Nomor Baris MySQL dan aplikasinya

Fungsi Nomor Baris MySQL & Kegunaannya

MySQL row_number() adalah fungsi jendela yang memberikan nomor baris berurutan ke setiap baris dalam set hasil. Nomor baris dimulai dengan 1. Anda dapat mempartisi tabel atau hasil kueri Anda ke dalam grup yang berbeda dan menetapkan nomor baris ke setiap baris secara berkelompok. Dalam hal ini, nomor baris pertama untuk setiap grup akan dimulai dengan 1

Berikut adalah sintaks dari fungsi row_number()

ROW_NUMBER() OVER (
   PARTITION BY <expression>,[{,<expression>}...]
   ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
)

Dalam pernyataan di atas, PARTITION BY mempartisi tabel atau kumpulan hasil ke dalam grup yang lebih kecil. Ekspresi untuk PARTITION BY akan menjadi satu atau lebih ekspresi yang digunakan dalam klausa GROUP BY kueri SQL. Harap dicatat, klausa PARTITION BY adalah opsional

Klausa ORDER BY mengurutkan baris dalam tabel Anda, atau hasil kueri. Jika Anda telah menggunakan klausa PARTITION BY, MySQL akan mengurutkan baris dalam setiap PARTITION secara terpisah. Harap diperhatikan, ORDER BY ini bekerja secara independen dari klausa ORDER BY kueri SQL Anda

Sekarang mari kita lihat beberapa aplikasi paling umum dari fungsi row_number()

Katakanlah Anda memiliki tabel berikut

mysql> create table prod_list(product varchar(10),amount int);

mysql> insert into prod_list(product, amount) values('a',10),('c',5),('b',20),('d',15);

mysql> select * from prod_list;
+---------+--------+
| product | amount |
+---------+--------+
| a       |     10 |
| c       |     20 |
| b       |      5 |
| d       |     15 |
+---------+--------+

1. Tetapkan Nomor Baris

Ini adalah kueri untuk menetapkan nomor baris berurutan ke tabel kami

SELECT 	
    product,
    amount,
ROW_NUMBER() OVER (
		ORDER BY product
	) row_num
FROM 
	prod_list
ORDER BY 
	product;

Inilah hasilnya

+---------+--------+---------+
| product | amount | row_num |
+---------+--------+---------+
| a       |     10 |   1     |
| b       |      5 |   2     |
| c       |     20 |   3     |
| d       |     15 |   4     |
+---------+--------+---------+

2. Temukan N baris teratas

Merupakan persyaratan umum oleh pengembang basis data dan pengembang web untuk mendapatkan N baris pertama dalam tabel atau grup baris. Anda dapat menanyakan hasil kueri di atas untuk mendapatkan baris N teratas dengan mudah. Berikut adalah contoh untuk mendapatkan 2 baris teratas dari tabel kami

mysql> select * from (
    SELECT 	
    product,
    amount,
    ROW_NUMBER() OVER (
		ORDER BY product
	) row_num
    FROM 
	prod_list
    ORDER BY 
	product
) tmp where row_num<=2;

+---------+--------+---------+
| product | amount | row_num |
+---------+--------+---------+
| a       |     10 |   1     |
| b       |      5 |   2     |
+---------+--------+---------+

Jika Anda memiliki banyak ekspresi di PARTITION BY maka MySQL akan mendapatkan N baris teratas untuk setiap partisi. Berikut adalah contoh untuk mendapatkan 2 baris teratas untuk setiap perwakilan penjualan

mysql> select * from (
    SELECT 	
    product,
    amount,
    ROW_NUMBER() OVER (
                PARTITION BY sales_rep
		ORDER BY product
	) row_num
    FROM 
	prod_list
    ORDER BY 
	product
) tmp where row_num<=2;

3. Untuk Paginasi

Anda juga dapat menggunakan kueri di atas untuk paginasi. Misalkan Anda memiliki daftar produk yang besar dan Anda ingin menampilkan produk untuk halaman ke-3, yaitu produk dengan nomor baris 21-30, maka inilah query SQL untuk itu

mysql> select * from (
    SELECT 	
    product,
    amount,
    ROW_NUMBER() OVER (
		ORDER BY product
	) row_num
    FROM 
	prod_list
    ORDER BY 
	product
) tmp where row_num>=21 and row_num<30;

Pada artikel ini, kita telah mempelajari cara menggunakan fungsi row_number() dan beberapa aplikasi umumnya

Fungsi ROW_NUMBER di MySQL

Fungsi ROW_NUMBER di MySQL adalah fungsi jendela atau fungsi analitik yang digunakan ketika kita ingin mengembalikan nomor urut unik mulai dari 1 untuk catatan di set hasil. Fungsi ROW_NUMBER() adalah fungsi bawaan di MySQL yang menetapkan nomor bilangan bulat berurutan yang unik untuk setiap baris mulai dari 1 dalam partisi dari kumpulan hasil. Nomor baris selalu dimulai dengan 1 untuk baris pertama di setiap partisi dan kemudian bertambah 1 untuk baris berikutnya dan seterusnya di setiap partisi

Sintaks untuk menggunakan fungsi ROW_NUMBER di MySQL diberikan di bawah ini

1. PARTISI DENGAN nilai_ekspresi. Ini opsional. Klausa PARTITION BY digunakan untuk mempartisi kumpulan hasil yang dihasilkan oleh klausa FROM dan kemudian fungsi ROW_NUMBER diterapkan ke setiap partisi. Di sini, value_expression menentukan nama kolom yang akan digunakan untuk mempartisi set hasil. Karena bersifat opsional, jika Anda tidak menentukan klausa PARTITION BY, maka fungsi ROW_NUMBER akan memperlakukan semua baris kueri sebagai satu partisi
2. Order_by_clause. Ini opsional. Klausa ORDER BY digunakan untuk menentukan urutan di mana setiap baris akan menetapkan ROW_NUMBER uniknya

Contoh Memahami Fungsi ROW_NUMBER di MySQL

Mari kita lihat memahami fungsi ROW_NUMBER di MySQL dengan beberapa contoh. Kita akan menggunakan tabel Karyawan berikut untuk memahami fungsi ROW_NUMBER

Silakan gunakan Skrip SQL di bawah ini untuk membuat database dan tabel serta mengisi tabel Karyawan dengan data sampel

CREATE DATABASE EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees
(
     ID INT PRIMARY KEY,
     Name VARCHAR(50),
     Department VARCHAR(50),
     Salary int
);
INSERT INTO Employees Values (1001, 'Sambit', 'IT', 15000);
INSERT INTO Employees Values (1002, 'Santosh', 'IT', 35000);
INSERT INTO Employees Values (1003, 'Manoj', 'HR', 15000);
INSERT INTO Employees Values (1004, 'Rakesh', 'Payroll', 35000);
INSERT INTO Employees Values (1005, 'Hina', 'IT', 42000);
INSERT INTO Employees Values (1006, 'Bikash', 'HR', 15000);
INSERT INTO Employees Values (1007, 'Priya', 'HR', 67000);
INSERT INTO Employees Values (1008, 'Preety', 'Payroll', 67000);
INSERT INTO Employees Values (1009, 'Priyanka', 'Payroll', 55000);
INSERT INTO Employees Values (1010, 'Anurag', 'Payroll', 15000);
INSERT INTO Employees Values (1011, 'Rishav', 'HR', 55000);
INSERT INTO Employees Values (1012, 'Dravid', 'IT', 96000);

Fungsi ROW_NUMBER tanpa PARTITION BY Clause di MySQL

Mari kita lihat dulu contoh fungsi ROW NUMBER tanpa menggunakan Klausa PARTITION BY di MySQL. Seperti yang telah kita diskusikan, jika kita tidak menentukan Klausa PARTITION BY di MySQL, maka fungsi ROW_NUMBER akan memperlakukan seluruh kumpulan hasil sebagai satu partisi. Akibatnya, fungsi ROW_NUMBER akan memberikan urutan penomoran berurutan untuk semua baris yang ada dalam kumpulan hasil berdasarkan kolom yang ditentukan dalam klausa urutan demi

Untuk pemahaman yang lebih baik, silakan lihat contoh di bawah ini di mana kami menggunakan fungsi MySQL ROW_NUMBER tanpa menggunakan klausa PARTITION BY

SELECT Id, Name, Department, Salary,
ROW_NUMBER() OVER (ORDER BY Department) AS RowNumber
FROM Employees;

Setelah Anda menjalankan kueri di atas, Anda akan mendapatkan output berikut. Jika Anda mengamati output di bawah ini, Anda dapat melihat bahwa tidak ada partisi dan oleh karena itu semua baris diberi nomor urut berurutan mulai dari 1 hingga 12 berdasarkan kolom Departemen

Jika Anda tidak menentukan klausa ORDER BY di ROW_NUMBER maka, itu akan memberikan nomor unik berdasarkan set hasil default. Misalnya, jalankan kueri di bawah ini tanpa klausa ORDER BY

Fungsi MySQL Row_Number dengan Klausa PARTITION BY

Mari kita lihat contoh fungsi MySQL ROW_NUMBER dengan Klausa PARTITION BY. Ketika Anda menentukan Klausa PARTITION BY, maka data dipartisi berdasarkan kolom yang Anda tentukan pada klausa Partition by. Silakan lihat kode berikut untuk memahami ini dengan lebih baik. Seperti yang Anda lihat, di sini, kami telah menetapkan Departemen dalam klausa Partisi Menurut dan Nama dalam klausa Urut Berdasarkan

SELECT Id, Name, Department, Salary,
ROW_NUMBER() OVER 
(
 PARTITION BY Department
 ORDER BY Name
) AS RowNumber
FROM Employees;

Setelah Anda menjalankan kueri di atas, Anda akan mendapatkan output berikut. Di tabel Karyawan kami, kami memiliki tiga departemen. Jadi, Partition by Clause akan membagi semua baris menjadi tiga partisi (IT, HR, dan Payroll). Kemudian pada setiap partisi, data diurutkan berdasarkan kolom Nama. Terakhir, fungsi ROW_NUMBER memberikan nomor urut unik mulai dari 1 hingga setiap partisi. Karena kami memiliki tiga partisi, setiap partisi akan dimulai dari 1 dan yang dapat Anda lihat pada gambar di bawah

Contoh real-time dari fungsi ROW_NUMBER di MySQL

Mari kita lihat satu contoh real-time dari fungsi ROW NUMBER di SQL Server. Untuk memahami ini, kita akan menggunakan tabel Karyawan berikut. Disini sengaja kita menyisipkan beberapa data duplikat ke dalam tabel Karyawan

Silakan gunakan Skrip SQL di bawah ini untuk membuat tabel Karyawan serta mengisi tabel Karyawan dengan data sampel yang diperlukan

CREATE TABLE Employee
(
     ID INT PRIMARY KEY,
     EmployeeCode VARCHAR(50),
     EmployeeName VARCHAR(50),
     Department VARCHAR(50),
     Salary int
);

INSERT INTO Employee Values (1, 'EMP1001', 'Rakesh', 'IT', 15000);
INSERT INTO Employee Values (2, 'EMP1001', 'Rakesh', 'IT', 15000);
INSERT INTO Employee Values (3, 'EMP1002', 'Priya', 'HR', 25000);
INSERT INTO Employee Values (4, 'EMP1002', 'Priya', 'HR', 25000);
INSERT INTO Employee Values (5, 'EMP1002', 'Priya', 'HR', 25000);
INSERT INTO Employee Values (6, 'EMP1003', 'Anurag', 'HR', 35000);
INSERT INTO Employee Values (7, 'EMP1003', 'Anurag', 'HR', 35000);
INSERT INTO Employee Values (8, 'EMP1003', 'Anurag', 'HR', 35000);
INSERT INTO Employee Values (9, 'EMP1003', 'Anurag', 'HR', 35000); 

Menghapus Duplikat Record di MySQL menggunakan Fungsi Row_Number

Sekarang kita perlu menghapus catatan duplikat dari tabel Karyawan. Setelah kami menghapus catatan duplikat, maka tabel Karyawan kami harus memiliki tiga catatan berikut

Kueri SQL berikut akan menghapus rekaman duplikat dari tabel Karyawan. Di sini, klausa PARTITION BY membagi hasil yang ditetapkan menjadi partisi berdasarkan kolom EmployeeCode. Artinya akan membuat beberapa partisi berdasarkan nilai kolom Employee dan memberikan nomor urut mulai dari 1 untuk setiap partisi. Kemudian pernyataan DELETE akan menghapus catatan dari setiap partisi di mana RowNumber lebih besar dari 1. Ini berarti hanya akan menyimpan satu record dari setiap partisi dan menghapus sisanya

WITH DeleteDuplicateEmployees
AS 
(
    SELECT Id, EmployeeCode, EmployeeName, Department, Salary,
 ROW_NUMBER() OVER 
     	( 
  PARTITION BY EmployeeCode
  ORDER BY Id
 ) RowNumber
    FROM Employee
)
DELETE FROM Employee USING Employee JOIN DeleteDuplicateEmployees 
ON Employee.Id = DeleteDuplicateEmployees.Id
WHERE DeleteDuplicateEmployees.RowNumber > 1;

Di sini, kami menggunakan ekspresi tabel umum (CTE) untuk mengembalikan baris duplikat dengan menggunakan fungsi Nomor Baris, lalu kami menggunakan pernyataan hapus untuk menghapus rekaman duplikat dari tabel Karyawan

Catatan. Poin yang harus Anda ingat adalah, MySQL tidak mendukung penghapusan berbasis CTE. Oleh karena itu, kita harus menggabungkan tabel asli dengan CTE untuk mencapai hal yang sama

Menemukan N baris teratas dari setiap grup menggunakan fungsi Nomor Baris di MySQL

Untuk memahami cara menemukan n baris teratas dari setiap grup, kita akan menggunakan tabel Siswa berikut. Seperti yang Anda lihat, di sini, kami mengkategorikan mahasiswa ke dalam tiga departemen i. e. CSE, DLL, dan SIPIL

Silakan gunakan Skrip SQL berikut untuk membuat tabel Siswa serta mengisi tabel Siswa dengan data yang diperlukan

CREATE TABLE Students
(
     Id INT PRIMARY KEY,
     Name VARCHAR(50),
     Department VARCHAR(50),
     Marks int
);

INSERT INTO Students Values (1001, 'Sambit', 'CSE', 750);
INSERT INTO Students Values (1002, 'Santosh', 'CSE', 740);
INSERT INTO Students Values (1003, 'Manoj', 'ETC', 755);
INSERT INTO Students Values (1004, 'Rakesh', 'CIVIL', 730);
INSERT INTO Students Values (1005, 'Hina', 'CSE', 770);
INSERT INTO Students Values (1006, 'Bikash', 'ETC', 765);
INSERT INTO Students Values (1007, 'Priya', 'ETC', 745);
INSERT INTO Students Values (1008, 'Preety', 'CIVIL', 740);
INSERT INTO Students Values (1009, 'Priyanka', 'CIVIL', 720);
INSERT INTO Students Values (1010, 'Anurag', 'CIVIL', 750);
INSERT INTO Students Values (1011, 'Rishav', 'ETC', 750);
INSERT INTO Students Values (1012, 'Dravid', 'CSE', 760);
INSERT INTO Students Values (1013, 'Sachin', 'CIVIL', 710);
INSERT INTO Students Values (1014, 'Mahesh', 'ETC', 770);
INSERT INTO Students Values (1015, 'Dinesh', 'CSE', 730);

Kita dapat menggunakan fungsi ROW_NUMBER() untuk menemukan N baris teratas untuk setiap grup. Sebagai contoh, mari kita lihat bagaimana menemukan tiga mahasiswa terbaik dari setiap jurusan berdasarkan nilai tertinggi. Pernyataan SQL berikut menggunakan ROW_NUMBER() untuk menemukan tiga Siswa teratas dari Setiap Departemen berdasarkan nilai tertinggi

WITH HighestMarkStudents
AS 
(
    SELECT Id, Name, Department, Marks,
 ROW_NUMBER() OVER 
   	( 
  PARTITION BY Department
  ORDER BY Marks DESC
 ) RowNumber
    FROM Students
)
 SELECT Id, Name, Department, Marks 
 FROM HighestMarkStudents
 WHERE RowNumber <= 3;

Setelah Anda menjalankan kueri di atas, Anda akan mendapatkan hasil yang diinginkan berikut

Paginasi menggunakan fungsi ROW_NUMBER() di MySQL

Karena MySQL ROW_NUMBER() menetapkan nomor integer unik untuk setiap baris dalam hasil, kita dapat menggunakannya untuk paginasi. Misalnya, kita perlu menampilkan daftar Siswa dengan 5 siswa per halaman. Untuk mendapatkan semua Siswa untuk halaman kedua, kita perlu menggunakan kueri SQL berikut

SELECT * FROM 
    (
         SELECT Id, Name, Department, Marks,
         ROW_NUMBER() OVER  (ORDER BY Id) AS RowNumber
         FROM Students
    ) temp
WHERE RowNumber BETWEEN 6 AND 10;   

Setelah Anda menjalankan kueri di atas, Anda akan mendapatkan hasil yang diinginkan berikut

Pada artikel selanjutnya, saya akan membahas fungsi RANK di MySQL beserta contohnya. Di sini, pada artikel ini, saya mencoba menjelaskan kebutuhan dan penggunaan fungsi ROW_NUMBER di MySQL dengan Contoh. Saya harap Anda menikmati fungsi ROW NUMBER ini di artikel MySQL

Bagaimana cara mendapatkan Rownum di SQL?

Apa yang setara dengan Rownum di MySQL?

Apakah Rownum tersedia di SQL Server?