A SELECT statement with TOP clause options

TOP clause can limits the numbers of rows return by the SELECT query .There are three options used with TOP clause in the SELECT Query:

1. TOP with number of rows
2. TOP with percentage of rows
3. TOP with ties

So let's see these examples to demonstrate the options:

USE AdventureWorks
GO

-- To return only 2 rows from the result set 
SELECT TOP 2 Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice

--TO return only the 2% from the result set 
SELECT TOP 2 PERCENT Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice

-- TO return only 2 rows from the result set 
-- plus : extra rows will returned if their values
-- (Based on ORDER BY column) match the vlaue of last row
SELECT TOP 2 WITH TIES Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice DESC