Mendapatkan Top x Record

Kita mungkin sering mendapatkan masalah untuk menampilkan x data terbaik dari suatu tabel database. Misalnya, tiga mahasiswa yang memiliki indeks prestasi kumulatif terbaik di suatu jurusan. Tentu saja, solusi satu-satunya yang bisa dilakukan adalah menggunakan SQL (Structured Query Language) untuk bertransaksi dengan database. Masalahnya, bagaimana sintaks-nya?

Kita bisa mendapatkannya dengan mengurutkan (sorting) kolom yang akan dirangking dengan klausa ORDER BY [column] dan mendapatkan x record teratasnya. Untuk mengambil x record teratas, Oracle memiliki ROWNUM, SQL Server memiliki TOP, dan MySQL memiliki LIMIT. Akan tetapi, bagaimana jika dalam record tersebut dikelompokkan menjadi beberapa group dan kita ingin merangking masing-masing kelompok tersebut sendiri-sendiri dan menampilkannya dalam satu data set? Misalnya, “Tampilkan 3 IPK terbaik mahasiswa dari masing-masing angkatan”. Artikel berikut ini membahas solusi permasalahan ini pada Oracle PL/SQL.

Oracle PL/SQL memiliki fungsi RANK() yang bertugas untuk memberikan rangking record berdasarkan suatu kolom. Ia juga bisa mengelompokkan kolom tersebut menjadi beberapa group dengan kategorisasi yang telah kita tentukan. Berikut ini adalah sintaks umumnya:

RANK() OVER( [PARTITION BY partition_clause]
ORDER BY column [ASC | DESC [NULLS FIRST | LAST ] )

ORDER BY column adalah kolom dimana kita akan merangking berdasarkan kolom tersebut. ASC | DESC adalah opsional, kita akan mengurutkannya dari kecil ke besar ataukah sebaliknya. NULLS FIRST | LAST adalah klasifikasi peletakan nilai NULL, di awal record ataukah di akhir record . PARTITION BY partition_clause adalah sintaks untuk mengklasifikasi kolom yang sedang dirangking berdasarkan kategori tertentu yang disebutkan di partition_clause. Sintaks ini mirip sintaks GROUP BY yang mengikuti fungsi aggregat (misalnya AVG, MAX, MIN).

Studi Kasus

Agar lebih jelas, mari kita bahas sebuah contoh. Katakanlah kita memiliki tabel FRS (Formulir Rencana Studi) seluruh mahasiswa yang berisi data berikut ini:

Tabel FRS
Sementara itu, untuk menghitung IPK, kita memerlukan jumlah SKS untuk masing-masing kode mata kuliah (ID_MK). Informasi tersebut disediakan oleh tabel matakuliah berikut ini:

Tabel Matakuliah

Pembahasan Masalah

Kita perlu menghitung IPK tiap-tiap mahasiswa (NRP) terlebih dahulu sebelum merangking semua mahasiswa. Rumus untuk menghitung IPK adalah sebagai berikut:

IPK = Jumlah semua (Nilai Matakuliah dalam angka * Jumlah SKS Matakuliah tersebut) dibagi dengan jumlah SKS Matakuliah yang telah ditempuh.

Script SQL-nya adalah sbb:

SELECT frs.nrp,
SUM (matakuliah.sks * konversi (frs.nilai_huruf)) /
SUM (matakuliah.sks) ipk
FROM frs,
matakuliah,
(SELECT frs.nrp, frs.id_mk, MAX (frs.tahun) tahun
FROM frs
GROUP BY frs.id_mk, frs.nrp) akhir
WHERE frs.nrp = akhir.nrp
AND frs.id_mk = akhir.id_mk
AND frs.tahun = akhir.tahun
AND matakuliah.id_mk = frs.id_mk
GROUP BY frs.nrp;

Di sini saya mengambil nilai yang ditempuh terakhir jika suatu mahasiswa mengulang suatu matakuliah yang dikerjakan dalam blok akhir.

Kemudian setelah itu kita rangking dengan fungsi RANK(). Script SQL-nya adalah sbb:

SELECT * FROM
(
SELECT RANK() OVER ( PARTITION BY SUBSTR( nrp, 1, 4 )
ORDER BY ipk DESC NULLS LAST ) ranking,
nrp, ipk FROM
(
SELECT frs.nrp,
SUM( matakuliah.sks * konversi( frs.nilai_huruf ) ) /
SUM( matakuliah.sks ) IPK
FROM frs, matakuliah,
(
SELECT frs.nrp, frs.id_mk, MAX (frs.tahun) tahun
FROM frs
GROUP BY frs.id_mk, frs.nrp
) akhir
WHERE frs.nrp = akhir.nrp
AND frs.id_mk = akhir.id_mk
AND frs.tahun = akhir.tahun
AND matakuliah.id_mk = frs.id_mk
GROUP BY frs.nrp
)
) topx
WHERE topx.ranking <= 3

Setiap format NRP memiliki format [2 digit jurusan][2 digit angkatan][1 digit program jurusan][2 digit kelas reguler/ekstensi][3 digit nomor urut]. Sehingga untuk mengambil tiap angkatan, saya melakukan substring dengan fungsi SUBSTR hingga indeks ke-4, sehingga menjadi 5102, 5101, atau 5100. Hasilnya? Hehehe… 😀 ini dia B-)
Tabel Hasil

Published
Categorized as ITisMyWorld

By Galih Satriaji

Bookaholic, Workaholic. Chubby. That's me!

4 comments

  1. Wah – wah … membuka ‘aib’ jurusan nih !
    BTW yang angkatan 2002 peringkat 1 dan 2 nya kok nilainya sama ? Siapa kira – kira ?

    Trus angkatan 2004 kok nggak ditampilkan ? 😀

Leave a comment

Your email address will not be published. Required fields are marked *