Menggunakan fungsi peringkat dan kumpulan baris
Fungsi peringkat dan set baris bukan fungsi skalar karena tidak mengembalikan satu nilai pun. Fungsi-fungsi ini menerima sekumpulan baris sebagai input dan mengembalikan sekumpulan baris sebagai output.
Fungsi peringkat
Fungsi peringkat memungkinkan Anda melakukan penghitungan terhadap sekumpulan baris yang ditentukan pengguna. Fungsi-fungsi ini termasuk fungsi peringkat, offset, agregat, dan distribusi.
Contoh ini menggunakan fungsi RANK untuk menghitung peringkat berdasarkan ListPrice, dengan harga tertinggi di peringkat 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Hasil kueri mungkin terlihat seperti:
ProductID
Nama
ListPrice
RankByPrice
749
Road-150 Merah, 62
3578,27
1
750
Road-150 Merah, 44
3578,27
1
751
Road-150 Merah, 48
3578,27
1
771
Mountain-100 Perak, 38
3399,99
4
772
Mountain-100 Perak, 42
3399,99
4
775
Mountain-100 Hitam, 38
3374,99
6
...
...
...
...
ATAS
Anda dapat menggunakan klausul OVER untuk menentukan partisi, atau pengelompokan dalam data. Misalnya, kueri berikut memperluas contoh sebelumnya untuk menghitung peringkat berbasis harga untuk produk dalam setiap kategori.
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
Hasil kueri ini mungkin terlihat seperti ini:
Kategori
Produk
ListPrice
RankByPrice
Celana Pendek Bib
Celana Pendek Bib Pria, S
89.99
1
Celana Pendek Bib
Celana Bib untuk Pria, M
89.99
1
Rak Sepeda
Hitch Rack - Empat Sepeda
120
1
Stand Sepeda
All-Purpose Penyangga Sepeda
159
1
Botol dan Kandang
Kandang Botol Gunung
9.99
1
Botol dan Kandang
Kandang Botol Jalan
8.99
2
Botol dan Kandang
Botol Air - 30 ons
4,99
3
Kurung Bawah
HL Bottom Bracket
121.49
1
Kurung Bawah
Kurung Bawah ML
101.24
2
Kurung Bawah
Kurung Bawah LL
53.99
3
...
...
...
...
Nota
Perhatikan bahwa beberapa baris memiliki nilai peringkat yang sama dan beberapa nilai dilewati. Ini karena kita hanya menggunakan RANK. Tergantung pada persyaratannya, Anda mungkin ingin menghindari ikatan pada nilai peringkat yang sama. Anda dapat mengontrol nilai peringkat dengan fungsi lain, DENSE_RANK, NTILE, dan ROW_NUMBER, sesuai kebutuhan. Untuk detail tentang fungsi-fungsi ini, lihat dokumentasi referensiTransact-SQL.
Fungsi himpunan baris
Fungsi set baris mengembalikan tabel virtual yang dapat digunakan dalam klausa FROM sebagai sumber data. Fungsi-fungsi ini mengambil parameter khusus untuk fungsi set baris itu sendiri. Mereka termasuk OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML, dan OPENJSON.
Fungsi OPENDATASOURCE, OPENQUERY, dan OPENROWSET memungkinkan Anda meneruskan kueri ke server database jarak jauh. Server jarak jauh kemudian akan mengembalikan sekumpulan baris hasil. Misalnya, kueri berikut menggunakan OPENROWSET untuk mendapatkan hasil kueri dari instans SQL Server bernama SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Untuk menggunakan server jarak jauh, Anda harus mengaktifkan beberapa opsi tingkat lanjut di instans SQL Server tempat Anda menjalankan kueri.
Fungsi OPENXML dan OPENJSON memungkinkan Anda mengkueri data terstruktur dalam format XML atau JSON dan mengekstrak nilai ke dalam set baris tabular.
Eksplorasi terperinci dari fungsi set baris berada di luar cakupan modul ini. Untuk informasi selengkapnya, lihat dokumentasi referensiTransact-SQL.