Penggabungan Untuk Mencari Nilai Minimum dan Maksimum Group – MySQL Dasar #76

Selanjutnya disini kita akan belajar untuk mencari rekaman pada tiap grup didalam sebuah tabel yang memuat nilai maksimun atau minimum pda kolom tertentu. 

Lihat daftar isi : Menggunakan Multi Tabel - MySQL Dasar #72 

Dimisalkan Anda ingin mengetahui lukisan paling mahal untuk setiap seniman Maka solusinya adalah dengan membuat sebuah tabel temporer untuk menampung nilai maksimun atau nilai minimun tiap grup, kemudian menggabungkan tabel temporer tersebut dengan tabel awal untuk mencari rekaman yang cocok untuk tiap grup. 

MariaDB [root93]> SELECT @maks := MAX(populasi) FROM propinsi;
+------------------------+
| @maks := MAX(populasi) |
+------------------------+
|              107383746 |
+------------------------+
1 row in set (0.001 sec)

MariaDB [root93]> SELECT * FROM propinsi WHERE populasi = @maks;
+----------------+-----------+------------+-----------+
| nama           | singkatan | pendirian  | populasi  |
+----------------+-----------+------------+-----------+
| Sumatera Barat | SUMBAR    | 1719-05-25 | 107383746 |
+----------------+-----------+------------+-----------+
1 row in set (0.001 sec)

Cara lain untuk menjawab pertanyaan ini adalah dengan menggunakan penggabungan. Pertama, untuk memilih nilai populasi maksimum ke dalam sebuah tabel temporer adalah sebagai berikut :

MariaDB [root93]> CREATE TABLE tmp SELECT MAX(populasi) AS popmaks
    -> FROM propinsi;
Query OK, 1 row affected (0.270 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT propinsi.* FROM propinsi,tmp
    -> WHERE propinsi.populasi=tmp.popmaks;
+----------------+-----------+------------+-----------+
| nama           | singkatan | pendirian  | populasi  |
+----------------+-----------+------------+-----------+
| Sumatera Barat | SUMBAR    | 1719-05-25 | 107383746 |
+----------------+-----------+------------+-----------+

MariaDB [root93]> SELECT @harga_lukisan :=MAX(harga) FROM lukisan;
+-----------------------------+
| @harga_lukisan :=MAX(harga) |
+-----------------------------+
|                          87 |
+-----------------------------+
1 row in set (0.001 sec)

MariaDB [root93]> SELECT seniman.nama, lukisan.judul, lukisan.harga
    -> FROM seniman, lukisan
    -> WHERE lukisan.harga=@harga_lukisan
    -> AND seniman.a_id=lukisan.a_id;
+----------+---------------+-------+
| nama     | judul         | harga |
+----------+---------------+-------+
| Da Vinci | The Mona Lisa |    87 |
+----------+---------------+-------+
1 row in set (0.001 sec)

Teknik yang sama juga bisa kita lakukan dengan cara membuat sebuah tabel temporer untuk menampung setiap harga maksimum, dan kemudian menggabungkannya dengan tabel – tabel lain.

MariaDB [root93]> CREATE TABLE temp SELECT MAX(harga) AS harga_maks
    -> FROM lukisan;
Query OK, 1 row affected (0.275 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT seniman.nama, lukisan.judul, lukisan.harga
    -> FROM seniman, lukisan, temp
    -> WHERE lukisan.harga=temp.harga_maks
    -> AND lukisan.a_id=seniman.a_id;
+----------+---------------+-------+
| nama     | judul         | harga |
+----------+---------------+-------+
| Da Vinci | The Mona Lisa |    87 |
+----------+---------------+-------+
1 row in set (0.001 sec)

Query sebelumnya hanya menampilkan informasi untuk lukisan yang paling mahal pada tabel lukisan. Bagaimana misalnya pertanyaanya adalah “Lukisan apa yang paling mahal untuk tiap seniman ?” Kita tidak bisa menggunakan satu variabel SQL untuk menjawab pertanyaan ini, karena jawabannya akan mencari satu harga per seniman, dan sebuah variabel untuk menampung hanya satu nilai pada satu waktu. Teknik menggunakan tabel temporer dapat diterapkan karena tabel dapat menampung banyak nilai dan operasi penggabungan dapat dilakukan untuk mencari semua kecocokan sekaligus. 

MariaDB [root93]> CREATE TABLE temporer
    -> SELECT a_id, MAX(harga) AS harga_maks
    -> FROM lukisan GROUP by a_id;
Query OK, 3 rows affected (0.299 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT seniman.nama, lukisan.judul, lukisan.harga
    -> FROM seniman, lukisan, temporer
    -> WHERE lukisan.a_id=temporer.a_id
    -> AND lukisan.harga=temporer.harga_maks
    -> AND lukisan.a_id=seniman.a_id;
+----------+-------------------+-------+
| nama     | judul             | harga |
+----------+-------------------+-------+
| Da Vinci | The Mona Lisa     |    87 |
| Van Gogh | The Potato Eaters |    67 |
| Renoir   | Les Deux Soeurs   |    64 |
+----------+-------------------+-------+
3 rows in set (0.011 sec)

MariaDB [root93]> SELECT nama, tanggal_travel, km
    -> FROM daftar_supir
    -> ORDER by nama, tanggal_travel;
+-------+----------------+------+
| nama  | tanggal_travel | km   |
+-------+----------------+------+
| Ben   | 2014-11-29     |  131 |
| Ben   | 2014-12-02     |  837 |
| Henri | 2014-11-26     |  380 |
| Henri | 2014-11-27     |  367 |
| Henry | 2014-11-26     |  278 |
| Henry | 2014-11-29     |  300 |
| Henry | 2014-11-30     |  574 |
| Henry | 2014-12-01     |  273 |
| Susi  | 2014-11-29     |  391 |
| Susi  | 2014-12-02     |  502 |
+-------+----------------+------+
10 rows in set (0.001 sec)

Salah satu permasalahan maksimum per grup untuk tabel ini adalah menunjukan perjalanan yang paling terakhir untuk tiap supir

MariaDB [root93]> CREATE TABLE tmpl
    -> SELECT nama, MAX(tanggal_travel) AS tanggal_travel
    -> FROM daftar_supir
    -> GROUP by nama;
Query OK, 4 rows affected (0.212 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT daftar_supir.nama, daftar_supir.tanggal_travel,
    -> daftar_supir.km
    -> FROM daftar_supir, tmpl
    -> WHERE daftar_supir.nama=tmpl.nama
    -> AND daftar_supir.tanggal_travel=tmpl.tanggal_travel
    -> ORDER by daftar_supir.nama;
+-------+----------------+------+
| nama  | tanggal_travel | km   |
+-------+----------------+------+
| Ben   | 2014-12-02     |  837 |
| Henri | 2014-11-27     |  367 |
| Henry | 2014-12-01     |  273 |
| Susi  | 2014-12-02     |  502 |
+-------+----------------+------+
4 rows in set (0.030 sec)