SQL: With Ties

When you need to return the top number of rows in a result set you would use SELECT TOP which can be a percentage or number e.g.

SELECT TOP 5% … etc
SELECT TOP 5 … etc 

This is fairly simple but what if the fifth, sixth and seventh rows had the same value.  You would probably want to return them as well.  This can be achieved by using WITH TIES e.g. 

SELECT TOP 5 WITH TIES EmployeeID, Salary
FROM Employee
ORDER BY 2 DESC  /* the 2 represents the second column */

The result set would have 7 rows. 

Also see With Ties – SQL Server Tip from Simon Harriyott’ blog for an excellent example.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s