TOP 1 WITH TIES

Okay, Lets start at the Top. (Afterall this blog is named TopOneWithTies.) The Good news is that, in SQL Server 2005, you can specify a variable as the argument for the Top clause. Wait, not just a variable, you can specify a sub query or a UDF as an argument for the TOP clause.

Also you can specify the TOP clause NOT just with the select, but also with INSERT, DELETE and UPDATE.

Sounds great right!. Lets see TOP in action.


USE AdventureWorks
GO
DECLARE @n int
SET @n = 5
SELECT TOP(@n) * FROM Sales.SalesOrderHeader

Now lets try to use a User Defined function as the input for TOP.

CREATE FUNCTION dbo.MyFunc()
RETURNS INT
AS
BEGIN
RETURN 3
END
GO

SELECT TOP(dbo.MyFunc()) * FROM Sales.SalesOrderHeader

And a subquery,

SELECT TOP ((SELECT COUNT(*) FROM Production.Product)) * FROM Sales.SalesOrderHeader

Now lets try to run some INSERT, UPDATE, DELTE Statements with the TOP Clause.


CREATE TABLE #TopSales(SalesOrderId int, OrderDate datetime)

INSERT TOP (5) INTO #TopSales
SELECT SalesOrderId, OrderDate
FROM Sales.SalesOrderHeader
ORDER By TotalDue DESC

SELECT * FROM #TopSales

UPDATE TOP (2) #TopSales
SET OrderDate = OrderDate +1

SELECT * FROM #TopSales

DELETE TOP (2) FROM #TopSales

SELECT * FROM #TopSales


Note that in case of UPDATE and DELETE you cannot specify an ORDER BY Clause (It seems).
So use this option carefully (for eg. Splitting the update of large no of rows into batches) as you cannot predict which rows will get affected. (I may have to do some further research on this)