TOP:
SELECT TOP (5) productid, unitprice
FROM Production.Products
WHERE categoryid = 1
ORDER BY unitprice DESC;
SELECT TOP (5) WITH TIES productid, unitprice
FROM Production.Products
WHERE categoryid = 1
ORDER BY unitprice DESC;
the first query will return exactly 5 records with the highest unit prices in category 1, while the second query may return more than 5 records if there are ties in the unit prices among the top 5 products.
Filtering Data with OFFSET-FETCH
SELECT * FROM Nums
ORDER BY n ASC
OFFSET 5 ROWS;
--This query will return all rows from the "Nums" table, but it will skip the first 5 rows. It effectively starts the result set from the 6th row onward.
SELECT * FROM Nums
ORDER BY n ASC
OFFSET 5 ROWS
FETCH NEXT 10 ROW ONLY;
--This query is similar to the first one, but it includes a FETCH clause. After skipping the first 5 rows using OFFSET, it fetches the next 10 rows only.
Uses the FETCH FIRST clause instead of FETCH NEXT. The result is the same; it retrieves the next 10 rows after skipping the first 5 rows.
Top used with Condition
-- Step 1: Declare variable
DECLARE @WebFlag BIT = 0;
-- Step 2: Create a temporary table
CREATE TABLE #EmployeeReports
(
ReportID INT,
EmployeeName NVARCHAR(100),
CreatedDate DATETIME
);
-- Step 3: Insert sample data
INSERT INTO #EmployeeReports (ReportID, EmployeeName, CreatedDate)
VALUES
(1, 'Alice', '2024-05-01'),
(2, 'Bob', '2024-04-01'),
(3, 'Charlie', '2024-04-10'),
(4, 'David', '2024-03-20'),
(5, 'Eve', '2024-03-15'),
(6, 'Frank', '2024-02-10'),
(7, 'Grace', '2024-01-25');
-- Step 4: Run the SELECT only if @WebFlag = 1
IF @WebFlag = 1
BEGIN
SELECT TOP 5 *
FROM #EmployeeReports
ORDER BY CreatedDate DESC;
END
ELSE
BEGIN
SELECT *
FROM #EmployeeReports
ORDER BY CreatedDate DESC;
END
-- Optional: Drop the temp table
DROP TABLE #EmployeeReports;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
SELECT ' +
CASE WHEN @WebFlag = 1 THEN 'TOP 5' ELSE '' END + '
* FROM #EmployeeReports
ORDER BY CreatedDate DESC;';
EXEC sp_executesql @SQL;WITH RankedReports AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS rn
FROM #EmployeeReports
)
SELECT *
FROM RankedReports
WHERE (@WebFlag = 1 AND rn <= 5)
OR (@WebFlag = 0);IF @WebFlag = 1
BEGIN
SELECT * FROM #EmployeeReports
ORDER BY CreatedDate DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
END
ELSE
BEGIN
SELECT * FROM #EmployeeReports
ORDER BY CreatedDate DESC;
ENDDynamic Top Value Select
DECLARE @top INT = 2;
CREATE TABLE #EmployeeReports
(
ReportID INT,
EmployeeName NVARCHAR(100),
CreatedDate DATETIME
);
INSERT INTO #EmployeeReports (ReportID, EmployeeName, CreatedDate)
VALUES
(1, 'Alice', '2024-05-01'),
(2, 'Bob', '2024-04-01'),
(3, 'Charlie', '2024-04-10'),
(4, 'David', '2024-03-20'),
(5, 'Eve', '2024-03-15'),
(6, 'Frank', '2024-02-10'),
(7, 'Grace', '2024-01-25');
SELECT TOP (@top) *
FROM #EmployeeReports
-- Optional: Drop the temp table
DROP TABLE #EmployeeReports;
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT TOP (' + CAST(@top AS NVARCHAR(10)) + ') * FROM #EmployeeReports ORDER BY CreatedDate DESC';
EXEC sp_executesql @sql;
SELECT *
FROM #EmployeeReports
ORDER BY CreatedDate DESC
OFFSET 0 ROWS FETCH NEXT @top ROWS ONLY;