September 7, 2024 mysql

Cara Menggunakan Order By Dengan Nilai Custom Di Mysql

Untuk mengurutkan order by dengan nilai custom di mysql, gunakan fungsi FIELD yang berisi nilai custom-nya, atau menggunakan statement CASE.

Contoh data.

select * from orders;

+----+------+---------+
| id | code | status  |
+----+------+---------+
|  1 | 001  | packed  |
|  2 | 002  | shipped |
|  3 | 003  | draft   |
|  4 | 004  | draft   |
|  5 | 005  | paid    |
|  6 | 006  | paid    |
|  7 | 006  | shipped |
+----+------+---------+

Contoh kasusnya data diatas ingin diurutkan berdasarkan yang statusnya paid dulu lalu yang draft dst.

Cara yang pertama bisa dilakukan dengan ORDER BY ditambah fungsi FIELD.

order by field(nama_kolom, status_1, status_2, dst)

Contohnya.

select * from orders
order BY FIELD(status, 'paid', 'draft', 'shipped', 'packed');

+----+------+---------+
| id | code | status  |
+----+------+---------+
|  5 | 005  | paid    |
|  6 | 006  | paid    |
|  3 | 003  | draft   |
|  4 | 004  | draft   |
|  2 | 002  | shipped |
|  7 | 006  | shipped |
|  1 | 001  | packed  |
+----+------+---------+

Cara kedua bisa menggunakan statement CASE.

order by case status
    when 'status_1' then nomor_urutan
    when 'status_2' then nomor_urutan
    else default_nomor_urutan
end

Contohnya.

select * from orders
order by case status
    when 'paid' then 1
    when 'draft' then 2
    when 'shipped' then 3
    when 'packed' then 4
    else 5
end;

+----+------+---------+
| id | code | status  |
+----+------+---------+
|  5 | 005  | paid    |
|  6 | 006  | paid    |
|  3 | 003  | draft   |
|  4 | 004  | draft   |
|  2 | 002  | shipped |
|  7 | 006  | shipped |
|  1 | 001  | packed  |
+----+------+---------+

Referensi