Fungsi over untuk menampilkan data pada TSQL Server

Dalam beberapa kasus pengambilan data dari database, terkadang kita ingin mengambil data tertinggi atau terendah dari suatu kumpulan baris data pada tabel. Solusi sederhana bisa menggunakan keyword TOP dalam pengambilan data, namun jika ditambah konstrain berdasarkan kategori tertentu ingin diambil data tertinggi/terendah anda tidak bisa menggunakan keyword TOP tapi menggunakan fungsi OVER(). Berikut ini detail penjelasannya

Misal dalam database kita ini terdapat dua tabel yaitu tabel produk dan jenis_produk dengan desain database berikut ini:

ERD Produk - Jenis

Berikut Query untuk menampilkan seluruh data dari tabel produk – jenis_produk:

SELECT p.id, p.nama, p.harga, j.nama AS jenis
FROM  produk p INNER JOIN jenis_produk j
ON p.idjenis = j.id ORDER BY p.harga DESC

Berikut hasil output query:

Jika menggunakan kata kunci TOP kita hanya dapat menampilkan data tertinggi atau data terendah saja. Query Berikut menampilkan dua data produk dengan harga tertinggi

SELECT TOP 2 p.id, p.nama, p.harga, j.nama AS jenis
FROM  produk p INNER JOIN jenis_produk j
ON p.idjenis = j.id ORDER BY p.harga DESC

Bagaimana jika kita ingin menampilkan dua harga tertinggi untuk masing-masing jenis produk ? berikut langkah-langkahnya

  1. Gunakan fungsi over dan row_number untuk mengambil data berdasarkan partisi kategori tertentu, dalam kasus ini kategori jenis_produk
    Berikut Querynya:

    SELECT ROW_NUMBER() OVER(PARTITION BY p.idjenis ORDER BY p.harga DESC)
    AS nourut,p.nama,p.harga,j.nama as jenis
    FROM produk p INNER JOIN jenis_produk j
    ON p.idjenis=j.id
    

  2. Gunakan Sub Query atau Query diatas dapat dijadikan VIEW untuk diambil dua data teratas dengan kriteria nourut yang lebih kecil dari 2. Pada contoh ini digunakan Sub Query untuk menampilkan dua data produk dengan harga tertinggi pada masing-masing kategori jenis produknya.
    SELECT nourut,nama,harga,jenis
    FROM
    (
    SELECT ROW_NUMBER() OVER(PARTITION BY p.idjenis ORDER BY p.harga DESC)
    AS nourut,p.nama,p.harga,j.nama as jenis
    FROM produk p INNER JOIN jenis_produk j
    ON p.idjenis=j.id
    ) A
    where nourut <=2
    

Selamat Mencoba

There Is 1 Response So Far. »

  1. [...] Jika penjelasan saya susah dipahami, anda bisa googling atau baca (diantaranya) link ini http://dev.xbata.com/database/sql-server/fungsi-over-untuk-menampilkan-data-tsql-server [...]

Post a Response