TOP Statement with PERCENT and WITH TIES Options

 

     It's always exciting to talk about the new features, but I don't want to forget about what is available out there today. I want to write about the TOP statement today. Probably it's one of the most common statements we use in SQL every day. Did you know that TOP has two very useful options?
 

PERCENT

     First one is [PERCENT], It indicates that the query returns only the first expression percent of rows from the result set. So you want to select %10 of a result set, use PERCENT. I am going to use the WideWorldImporters database for this. Here is an example.

SELECT count(*) as tenpercent from (
select top 10 percent * from sales.invoices) as temp

SELECT COUNT(*) FROM Sales.Invoices

It is pretty simple, and it can be really useful in some cases.

WITH TIES

    WITH TIES is used when you want to return two or more rows that toe for last place in the limited result set. You must use ORDER BY when you use WITH TIES. It may cause more rows to be returned than the value specified in the TOP statement. Here is an example.

This is the result set without any TOP statement.

Here is the result with a simple TOP statement.

SELECT TOP 2 salespersonpersonid, orderdate 

FROM SALES.ORDERS

WHERE orderdate='2016-1-1' and salespersonpersonid = 13

order by salespersonpersonid

   In this example, we are going to select top 2 orders on a specific date for a sales person. Here is the result for it.

    Now, Let's say we need to get top 2 orders on a specific date for this sales person but we want to include other orders if there is any on the specified date in the result set.

select top 2 with ties salespersonpersonid, orderdate

from sales.orders 
where orderdate='2016-1-1' and salespersonpersonid = 13  
order by salespersonpersonid

 
   WITH TIES option is going to get all orders on the specified date and it is going to add the additional rows to the result set. Here is the result.

    As you can see, it added one more row to our result set. We wanted top 2, and got 3 rows since there were total orders sold on this date.