TOP (Transact-SQL)

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) titik akhir analitik SQL di Microsoft Fabric Warehouse dalam database Microsoft FabricSQL di Microsoft Fabric

Membatasi baris yang dikembalikan dalam hasil kueri yang diatur ke jumlah baris atau persentase baris tertentu di SQL Server. Saat Anda menggunakan TOP dengan klausa ORDER BY, kumpulan hasil dibatasi pada jumlah baris pertama n yang diurutkan. Jika tidak, TOP mengembalikan jumlah baris n pertama dalam urutan yang tidak ditentukan. Gunakan klausa ini untuk menentukan jumlah baris yang dikembalikan dari pernyataan SELECT. Atau, gunakan TOP untuk menentukan baris yang terpengaruh oleh pernyataan INSERT, UPDATE, MERGE, atau DELETE.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server dan Azure SQL Database:

[
    TOP (expression) [ PERCENT ]
    [ WITH TIES ]
]

Sintaks untuk Azure Synapse Analytics dan Analytics Platform System (PDW):

[
    TOP ( expression )
    [ WITH TIES ]
]

Argumen

expression

Ekspresi numerik yang menentukan jumlah baris yang akan dikembalikan. ekspresi secara implisit dikonversi ke nilai float jika Anda menentukan . Jika tidak, ekspresi dikonversi ke bigint.

PERCENT

Menunjukkan bahwa kueri hanya mengembalikan persentase ekspresi pertama baris dari kumpulan hasil. Nilai pecahan dibulatkan ke atas ke nilai bilangan bulat berikutnya.

DENGAN IKATAN

Mengembalikan dua baris atau lebih yang mengikat untuk tempat terakhir dalam kumpulan hasil terbatas. Anda harus menggunakan argumen ini dengan klausa ORDER BY. WITH TIES dapat menyebabkan lebih banyak baris dikembalikan daripada nilai yang ditentukan dalam ekspresi . Misalnya, jika ekspresi diatur ke 5 tetapi dua baris lagi cocok dengan nilai kolom ORDER BY di baris 5, kumpulan hasil berisi tujuh baris.

Anda dapat menentukan klausa TOP dengan argumen WITH TIES hanya dalam pernyataan SELECT, dan hanya jika Anda juga menentukan klausa ORDER BY. Urutan rekaman pengikatan yang dikembalikan bersifat arbitrer. ORDER BY tidak memengaruhi aturan ini.

Praktik terbaik

Dalam pernyataan SELECT, selalu gunakan klausa ORDER BY dengan klausa TOP. Ini adalah satu-satunya cara untuk secara terprediksi menunjukkan baris mana yang dipengaruhi oleh TOP.

Gunakan OFFSET dan FETCH dalam klausa ORDER BY alih-alih klausul TOP untuk mengimplementasikan solusi paging kueri. Solusi halaman (yaitu, mengirim potongan atau halaman data ke klien) lebih mudah diterapkan menggunakan klausa OFFSET dan FETCH. Untuk informasi selengkapnya, lihat klausa SELECT - ORDER BY.

Gunakan TOP (atau OFFSET dan FETCH) alih-alih SET ROWCOUNT untuk membatasi jumlah baris yang dikembalikan. Metode ini lebih disukai daripada menggunakan SET ROWCOUNT karena alasan berikut:

• Sebagai bagian dari pernyataan SELECT, pengoptimal kueri dapat mempertimbangkan nilai ekspresi dalam klausa TOP atau FETCH selama pengoptimalan kueri. Karena Anda menggunakan SET ROWCOUNT di luar pernyataan yang menjalankan kueri, nilainya tidak dapat dipertimbangkan dalam rencana kueri.

Dukungan kompatibilitas

Untuk kompatibilitas mundur, tanda kurung bersifat opsional dalam pernyataan SELECT jika ekspresi adalah konstanta bilangan bulat. Kami menyarankan agar Anda selalu menggunakan tanda kurung untuk TOP dalam pernyataan SELECT. Melakukannya memberikan konsistensi dengan penggunaan yang diperlukan dalam pernyataan INSERT, UPDATE, MERGE, dan DELETE.

Interoperabilitas

Ekspresi TOP tidak memengaruhi pernyataan yang mungkin berjalan karena pemicu. Tabel inserted dan deleted dalam pemicu hanya mengembalikan baris yang benar-benar dipengaruhi oleh pernyataan INSERT, UPDATE, MERGE, atau DELETE. Misalnya, jika INSERT TRIGGER diaktifkan sebagai hasil dari pernyataan INSERT yang menggunakan klausa TOP.

SQL Server memungkinkan pembaruan baris melalui tampilan. Karena Anda dapat menyertakan klausa TOP dalam definisi tampilan, baris tertentu dapat menghilang dari tampilan jika baris tidak lagi memenuhi persyaratan ekspresi TOP karena pembaruan.

Ketika ditentukan dalam pernyataan MERGE, klausa TOP berlaku setelah seluruh tabel sumber dan seluruh tabel target digabungkan. Dan, baris gabungan yang tidak memenuhi syarat untuk tindakan sisipkan, perbarui, atau hapus dihapus. Klausa TOP semakin mengurangi jumlah baris yang digabungkan ke nilai yang ditentukan dan tindakan sisipkan, perbarui, atau hapus berlaku untuk baris gabungan yang tersisa dengan cara yang tidak diurutkan. Artinya, tidak ada urutan di mana baris didistribusikan di antara tindakan yang ditentukan dalam klausa WHEN. Misalnya, jika menentukan TOP (10) memengaruhi 10 baris, tujuh dari baris ini mungkin diperbarui, dan tiga disisipkan. Atau, satu mungkin dihapus, lima diperbarui, dan empat disisipkan, dan sebagainya. Karena pernyataan MERGE melakukan pemindaian tabel penuh dari tabel sumber dan target, performa I/O dapat terpengaruh saat Anda menggunakan klausa TOP untuk memodifikasi tabel besar dengan membuat beberapa batch. Dalam skenario ini, penting untuk memastikan bahwa semua batch berturut-turut menargetkan baris baru.

Berhati-hatilah saat Anda menentukan klausa TOP dalam kueri yang berisi operator UNION, UNION ALL, EXCEPT, atau INTERSECT. Dimungkinkan untuk menulis kueri yang mengembalikan hasil yang tidak terduga karena urutan klausa TOP dan ORDER BY diproses secara logis tidak selalu intuitif ketika operator ini digunakan dalam operasi tertentu. Misalnya, mengingat tabel dan data berikut, asumsikan bahwa Anda ingin mengembalikan mobil merah paling murah dan mobil biru paling murah. Artinya, sedan merah dan van biru.

CREATE TABLE dbo.Cars
(
    Model VARCHAR (15),
    Price MONEY,
    Color VARCHAR (10)
);

INSERT dbo.Cars
VALUES ('sedan', 10000, 'red'),
    ('convertible', 15000, 'blue'),
    ('coupe', 20000, 'red'),
    ('van', 8000, 'blue');

Untuk mencapai hasil ini, Anda mungkin menulis kueri berikut.

SELECT TOP (1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'red'
UNION ALL
SELECT TOP (1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'blue'
ORDER BY Price ASC;
GO

Berikut adalah hasil yang ditetapkan.

 Model         Color      Price
 ------------- ---------- -------
 sedan         red        10000.00
 convertible   blue       15000.00

Hasil yang tidak terduga dikembalikan karena klausa TOP berjalan secara logis sebelum klausa ORDER BY, yang mengurutkan hasil operator (UNION ALL dalam kasus ini). Jadi, kueri sebelumnya mengembalikan satu mobil merah dan satu mobil biru dan kemudian memesan hasil dari serikat tersebut dengan harga. Contoh berikut menunjukkan metode penulisan kueri ini yang benar untuk mencapai hasil yang diinginkan.

SELECT Model, Color, Price
FROM (SELECT TOP (1) Model, Color, Price
      FROM dbo.Cars
      WHERE Color = 'red'
      ORDER BY Price ASC) AS a
UNION ALL
SELECT Model, Color, Price
FROM (SELECT TOP (1) Model, Color, Price
      FROM dbo.Cars
      WHERE Color = 'blue'
      ORDER BY Price ASC) AS b;
GO

Dengan menggunakan TOP dan ORDER BY dalam operasi subpilih, Anda memastikan bahwa hasil klausa ORDER BY diterapkan ke klausa TOP dan tidak mengurutkan hasil operasi UNION.

Berikut adalah hasil yang ditetapkan.

 Model         Color      Price
 ------------- ---------- -------
 sedan         red        10000.00
 van           blue        8000.00

Keterbatasan

Saat Anda menggunakan TOP dengan INSERT, UPDATE, MERGE, atau DELETE, baris yang dirujuk tidak diatur dalam urutan apa pun. Dan, Anda tidak dapat secara langsung menentukan klausa ORDER BY dalam pernyataan ini. Jika Anda perlu menggunakan TOP untuk menyisipkan, menghapus, atau memodifikasi baris dalam urutan kronologis yang bermakna, gunakan TOP dengan klausa ORDER BY yang ditentukan dalam pernyataan subpilih. Lihat bagian Contoh di artikel ini.

Anda tidak dapat menggunakan TOP dalam pernyataan UPDATE atau DELETE pada tampilan yang dipartisi.

Anda tidak dapat menggabungkan TOP dengan OFFSET dan FETCH dalam ekspresi kueri yang sama (dalam cakupan kueri yang sama). Untuk informasi selengkapnya, lihat klausa SELECT - ORDER BY.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan database sampel AdventureWorks2022 atau AdventureWorksDW2022, yang dapat Anda unduh dari Sampel Microsoft SQL Server dan Proyek Komunitas beranda.

Sintaks dasar

Contoh di bagian ini menunjukkan fungsionalitas dasar klausa ORDER BY menggunakan sintaks minimum yang diperlukan.

J. Gunakan TOP dengan nilai konstanta

Contoh berikut menggunakan nilai konstanta untuk menentukan jumlah karyawan yang dikembalikan dalam kumpulan hasil kueri. Dalam contoh pertama, 10 baris pertama yang tidak ditentukan dikembalikan karena klausa ORDER BY tidak digunakan. Dalam contoh kedua, klausul ORDER BY digunakan untuk mengembalikan 10 karyawan teratas yang baru saja dipekerjakan.

USE AdventureWorks2022;
GO

-- Select the first 10 random employees.
SELECT TOP (10) JobTitle, HireDate
FROM HumanResources.Employee;
GO

-- Select the first 10 employees hired most recently.
SELECT TOP (10) JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY HireDate DESC;
GO

B. Menggunakan TOP dengan variabel

Contoh berikut menggunakan variabel untuk menentukan jumlah karyawan yang dikembalikan dalam kumpulan hasil kueri.

USE AdventureWorks2022;
GO

DECLARE @p AS INT = 10;

SELECT TOP (@p) JobTitle, HireDate, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours DESC;
GO

C. Tentukan persentase

Contoh berikut menggunakan PERCENT untuk menentukan jumlah karyawan yang dikembalikan dalam kumpulan hasil kueri. Ada 290 karyawan dalam HumanResources.Employee tabel. Karena lima persen dari 290 adalah nilai pecahan, nilai dibulatkan ke atas ke bilangan bulat berikutnya.

USE AdventureWorks2022;
GO

SELECT TOP (5) PERCENT JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY HireDate DESC;
GO

Sertakan nilai dasi

J. Gunakan WITH TIES untuk menyertakan baris yang cocok dengan nilai di baris terakhir

Contoh berikut mendapatkan persentase teratas 10 dari semua karyawan dengan gaji tertinggi dan mengembalikannya dalam urutan menurun sesuai dengan gaji mereka. Menentukan WITH TIES memastikan bahwa karyawan dengan gaji yang sama dengan gaji terendah yang dikembalikan (baris terakhir) juga disertakan dalam tataan hasil, bahkan jika melebihi 10 persentase karyawan.

USE AdventureWorks2022;
GO

SELECT TOP (10) PERCENT WITH TIES pp.FirstName,
                                  pp.LastName,
                                  e.JobTitle,
                                  e.Gender,
                                  r.Rate
FROM Person.Person AS pp
     INNER JOIN HumanResources.Employee AS e
         ON pp.BusinessEntityID = e.BusinessEntityID
     INNER JOIN HumanResources.EmployeePayHistory AS r
         ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;
GO

Batasi baris yang terpengaruh oleh DELETE, INSERT, atau UPDATE

J. Gunakan TOP untuk membatasi jumlah baris yang dihapus

Saat Anda menggunakan klausa TOP (<n>) dengan DELETE, operasi penghapusan dilakukan pada pilihan baris n yang tidak ditentukan. Artinya, pernyataan DELETE memilih jumlah baris (n) apa pun yang memenuhi kriteria yang ditentukan dalam klausa WHERE. Contoh berikut menghapus 20 baris dari PurchaseOrderDetail tabel yang memiliki tanggal jatuh tempo lebih awal dari 1 Juli 2002.

USE AdventureWorks2022;
GO

DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

Jika Anda ingin menggunakan TOP untuk menghapus baris dalam urutan kronologis yang bermakna, gunakan TOP dengan ORDER BY dalam pernyataan subpilih. Kueri berikut menghapus 10 baris PurchaseOrderDetail tabel yang memiliki tanggal jatuh tempo paling awal. Untuk memastikan bahwa hanya 10 baris yang dihapus, kolom yang ditentukan dalam pernyataan subpilih (PurchaseOrderID) adalah kunci utama tabel. Menggunakan kolom non-kunci dalam pernyataan subpilih dapat mengakibatkan penghapusan lebih dari 10 baris jika kolom yang ditentukan berisi nilai duplikat.

USE AdventureWorks2022;
GO

DELETE Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN (
    SELECT TOP 10 PurchaseOrderDetailID
    FROM Purchasing.PurchaseOrderDetail
    ORDER BY DueDate ASC
);
GO

B. Gunakan TOP untuk membatasi jumlah baris yang disisipkan

Contoh berikut membuat tabel EmployeeSales dan menyisipkan nama dan data penjualan tahunan hingga saat ini untuk lima karyawan teratas dari tabel HumanResources.Employee. Pernyataan INSERT memilih lima baris yang dikembalikan oleh pernyataan SELECT yang memenuhi kriteria yang ditentukan dalam klausa WHERE. Klausa OUTPUT menampilkan baris yang disisipkan ke dalam tabel EmployeeSales. Klausa ORDER BY dalam pernyataan SELECT tidak digunakan untuk menentukan lima karyawan teratas.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO

CREATE TABLE dbo.EmployeeSales
(
    EmployeeID NVARCHAR (11) NOT NULL,
    LastName NVARCHAR (20) NOT NULL,
    FirstName NVARCHAR (20) NOT NULL,
    YearlySales MONEY NOT NULL
);
GO

INSERT TOP (5) INTO dbo.EmployeeSales
OUTPUT
    inserted.EmployeeID,
    inserted.FirstName,
    inserted.LastName,
    inserted.YearlySales
SELECT sp.BusinessEntityID,
       c.LastName,
       c.FirstName,
       sp.SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN Person.Person AS c
         ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
GO

Jika Anda ingin menggunakan TOP untuk menyisipkan baris dalam urutan kronologis yang bermakna, gunakan TOP dengan ORDER BY dalam pernyataan subpilih. Contoh berikut menunjukkan cara melakukan ini. Klausa OUTPUT menampilkan baris yang disisipkan ke dalam tabel EmployeeSales. Lima karyawan teratas sekarang dimasukkan berdasarkan hasil klausul ORDER BY alih-alih baris yang tidak ditentukan.

INSERT INTO dbo.EmployeeSales
OUTPUT
    inserted.EmployeeID,
    inserted.FirstName,
    inserted.LastName,
    inserted.YearlySales
SELECT TOP (5) sp.BusinessEntityID,
               c.LastName,
               c.FirstName,
               sp.SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN Person.Person AS c
         ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.SalesYTD > 250000.00
ORDER BY sp.SalesYTD DESC;
GO

C. Gunakan TOP untuk membatasi jumlah baris yang diperbarui

Contoh berikut menggunakan klausa TOP untuk memperbarui baris dalam tabel. Saat Anda menggunakan klausa TOP (<n>) dengan UPDATE, operasi pembaruan berjalan pada jumlah baris yang tidak ditentukan. Artinya, pernyataan UPDATE memilih jumlah baris (n) apa pun yang memenuhi kriteria yang ditentukan dalam klausa WHERE. Contoh berikut menetapkan 10 pelanggan dari satu tenaga penjual ke tenaga penjual lainnya.

USE AdventureWorks2022;

UPDATE TOP (10)
Sales.Store
SET SalesPersonID = 276
WHERE SalesPersonID = 275;
GO

Jika Anda harus menggunakan TOP untuk menerapkan pembaruan dalam kronologi yang bermakna, Anda harus menggunakan TOP bersama dengan ORDER BY dalam pernyataan subpilih. Contoh berikut memperbarui jam liburan 10 karyawan dengan tanggal sewa paling awal.

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 BusinessEntityID
      FROM HumanResources.Employee
      ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;
GO

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

Contoh berikut mengembalikan 31 baris teratas yang cocok dengan kriteria kueri. Klausa ORDER BY memastikan bahwa 31 baris yang dikembalikan adalah 31 baris pertama berdasarkan urutan alfabet kolom LastName.

Menggunakan TOP tanpa menentukan ikatan.

SELECT TOP (31) FirstName, LastName
FROM DimEmployee
ORDER BY LastName;

Hasil: 31 baris dikembalikan.

Menggunakan TOP, menentukan WITH TIES.

SELECT TOP (31) WITH TIES FirstName, LastName
FROM DimEmployee
ORDER BY LastName;

Hasil: 33 baris dikembalikan, karena tiga karyawan bernama Brown dasi untuk baris ke-31.

Konten terkait

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• SELECT - klausa ORDER BY (Transact-SQL)
• SET ROWCOUNT (Transact-SQL)
• MERGE (Transact-SQL)