Mencari Nilai Maksimum Per Kategori SQL

Salah satu masalah paling umum yang melibatkan query database adalah mencari nilai terbesar/terkecil pada tiap kategori/tipe. Sebagai contoh adalah mencari pencetak gol terbanyak pada masing-masing dari 4 klub teratas sepak bola Liga Inggris berikut ini.

Nama                    Klub           Gol
------------------------------------------
Sergio Agüero           Man City       23
Edin Džeko              Man City       14
Mario Balotelli         Man City       13
Wayne Rooney	        Man United     27
Javier Hernández        Man United     10
Robin Van Persie        Arsenal        30
Emmanuel Adebayor       Tottenham      17
Jermain Defoe           Tottenham      11
Rafael Van der Vaart    Tottenham      11

Berikut ini adalah hasil yang diinginkan.

Nama                    Klub           Gol
------------------------------------------
Robin Van Persie        Arsenal        30
Wayne Rooney	        Man United     27
Sergio Agüero           Man City       23
Emmanuel Adebayor       Tottenham      17

Untuk menghasilkan keluaran seperti di atas, paling tidak kita membutuhkan dua langkah. Mencari jumlah gol yang diinginkan, kemudian tampilkan field lainnya berdasarkan gol yang sudah dicari tadi.

Langkah pertama adalah mencari gol terbanyak pada masing-masing klub tanpa melihat siapa pencetak golnya.

SELECT Klub, MAX(Gol) as maxGol 
FROM `skorer` 
GROUP BY Klub

Klub           maxGol
---------------------
Arsenal        30
Man City       23
Man United     27
Tottenham      17

Hasil bisa berbeda tergantung teknik penyortingan yang digunakan. Pada contoh di atas penyortingan menaik (ascending) berdasarkan field “Klub”.

Langkah kedua adalah menampilkan field sisanya (Nama) dengan cara menggabungkan table hasil query pertama dengan table mula-mula (skorer).

SELECT b.Nama, b.Klub, b.Gol
FROM (
  SELECT Klub, MAX(Gol) AS maxGol
  FROM `skorer` 
  GROUP BY Klub
) AS a
INNER JOIN `skorer` AS b ON 
  a.Klub = b.Klub AND a.maxGol = b.Gol

Nama                    Klub           Gol
------------------------------------------
Sergio Agüero           Man City       23
Wayne Rooney	        Man United     27
Robin Van Persie        Arsenal        30
Emmanuel Adebayor       Tottenham      17

Jika ingin diurutkan sesuai contoh, tinggal tambahkan ORDER BY Gol DESC pada akhir query.

Sebenarnya ada query yang lebih singkat untuk menampilkan hasil seperti di atas, yaitu dengan menggunakan sub-query untuk mencari gol terbanyak pada masing-masing klub.

SELECT * 
FROM `skorer`
WHERE Gol = (
  SELECT MAX(Gol) 
  FROM `skorer` AS a 
  WHERE a.Klub = skorer.Klub
)