Mengurutkan Hasil Ekspresi - MySQL Dasar #35

Disini Anda akan mengurutkan hasil query berdasarkan nilai-nilai yang dihitung dari suatu kolom. Anda bisa menempatkan ekspresi penghitung nilai di dalam klausa ORDER BY.

Daftar isi : Pengurutan Hasil Query 

Salah satu kolom pada tabel mail berikut menunjukan berapa besa setiap pesan mail dalam satuan byte

MariaDB [root93]> SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+--------+
| t                   | srcuser | srchost | dstuser | dsthost | ukuran |
+---------------------+---------+---------+---------+---------+--------+
| 2021-10-13 12:32:41 | barb    | saturn  | tricia  | mars    |  58274 |
| 2021-10-13 10:36:44 | tricia  | mars    | gene    | venus   |  19263 |
| 2021-10-13 10:37:20 | phil    | mars    | phil    | saturn  |   1048 |
| 2021-10-13 10:38:06 | barb    | saturn  | tricia  | venus   |    271 |
| 2021-10-13 10:38:54 | phil    | mars    | tricia  | saturn  |   5781 |
| 2021-10-13 10:39:37 | gene    | venus   | barb    | mars    |   2291 |
| 2021-10-13 10:40:07 | barb    | venus   | barb    | venus   |  98161 |
| 2021-10-13 10:40:45 | tricia  | saturn  | phil    | venus   | 239238 |
| 2021-10-13 10:41:27 | gene    | mars    | gene    | saturn  |   3623 |
| 2021-10-13 10:41:27 | gene    | saturn  | gene    | mars    |  32682 |
| 2021-10-13 10:42:32 | gene    | venus   | barb    | mars    |   3872 |
| 2021-10-13 10:42:32 | phil    | venus   | barb    | venus   |   7823 |
| 2021-10-13 10:43:39 | gene    | saturn  | gene    | venus   |  22332 |
+---------------------+---------+---------+---------+---------+--------+
13 rows in set (0.06 sec)

MariaDB [root93]> SELECT t, srcuser, FLOOR((ukuran+1023)/1024) FROM mail WHERE ukuran>5000 ORDER BY 3;
+---------------------+---------+---------------------------+
| t                   | srcuser | FLOOR((ukuran+1023)/1024) |
+---------------------+---------+---------------------------+
| 2021-10-13 10:38:54 | phil    |                         6 |
| 2021-10-13 10:42:32 | phil    |                         8 |
| 2021-10-13 10:36:44 | tricia  |                        19 |
| 2021-10-13 10:43:39 | gene    |                        22 |
| 2021-10-13 10:41:27 | gene    |                        32 |
| 2021-10-13 12:32:41 | barb    |                        57 |
| 2021-10-13 10:40:07 | barb    |                        96 |
| 2021-10-13 10:40:45 | tricia  |                       234 |
+---------------------+---------+---------------------------+
8 rows in set (0.02 sec)

MariaDB [root93]> SELECT t, srcuser, FLOOR((ukuran+1023)/1024) AS kilobyte FROM mail WHERE ukuran>5000 ORDER BY kilobyte;
+---------------------+---------+----------+
| t                   | srcuser | kilobyte |
+---------------------+---------+----------+
| 2021-10-13 10:38:54 | phil    |        6 |
| 2021-10-13 10:42:32 | phil    |        8 |
| 2021-10-13 10:36:44 | tricia  |       19 |
| 2021-10-13 10:43:39 | gene    |       22 |
| 2021-10-13 10:41:27 | gene    |       32 |
| 2021-10-13 12:32:41 | barb    |       57 |
| 2021-10-13 10:40:07 | barb    |       96 |
| 2021-10-13 10:40:45 | tricia  |      234 |
+---------------------+---------+----------+
8 rows in set (0.00 sec)

Perlu dicatat, ORDER BY 3 pada parameter diatas mengacu ke kolom ukuran karena posisi pemanggilannya didalam perintah SELECT berada diposisi ketiga

Selanjutnya parameter floor yang berisi ekspresi penghitung nilai bisa ditempatkan secara langsung di dalam klausa ORDER BY seperti berikut ini

MariaDB [root93]> SELECT t, srcuser, FLOOR((ukuran+1023)/1024) FROM mail WHERE ukuran>5000 ORDER BY FLOOR((ukuran+1023)/1024)
+---------------------+---------+---------------------------+
| t                   | srcuser | FLOOR((ukuran+1023)/1024) |
+---------------------+---------+---------------------------+
| 2021-10-13 10:38:54 | phil    |                         6 |
| 2021-10-13 10:42:32 | phil    |                         8 |
| 2021-10-13 10:36:44 | tricia  |                        19 |
| 2021-10-13 10:43:39 | gene    |                        22 |
| 2021-10-13 10:41:27 | gene    |                        32 |
| 2021-10-13 12:32:41 | barb    |                        57 |
| 2021-10-13 10:40:07 | barb    |                        96 |
| 2021-10-13 10:40:45 | tricia  |                       234 |
+---------------------+---------+---------------------------+
8 rows in set (0.00 sec)

Baca selanjutnya : Menampilkan Himpunan Nilai dengan Pengurutan Lain

PLEASE SHARE

Share it Share it Tweet Share it Pin it

Ahmad Zaelani

Seorang laki - laki biasa yang senang duduk sendirian di pinggir pantai, pengagum embun pagi dan lembayung senja, suka blogging, coding dan jogging. Moto hidupnya adalah hidup santai dan simple