• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Using SELECT TOP WITH TIES in SQL Server

June 11, 2014 by Andy Hayes 3 Comments

I’ve used SELECT TOP (x) many, many times. After learning how to use SELECT, it’s something I quickly learned to use and remains something which I continue to use often.

I’ve also used TOP (x) with UPDATE and DELETE statements to limit the number of rows updated or deleted in a statement. I’ve never had to use SELECT TOP WITH TIES though.

I’ve glanced through the documentation in the past and thought – “must learn that one day” but never had the time or need to. I guess some things you learn how to use only when you have to.

So this post is dedicated to my learning experience of using SELECT TOP WITH TIES 🙂

What does SELECT TOP WITH TIES do?

Let’s take an example. Say you wanted to retrieve the first 5 rows from a table and you ordered by a specific column. So you get back 10 rows ordered by your column.

Now apply SELECT TOP (5) WITH TIES and any row which has a column value that matches the last row’s column value as defined by the ORDER BY clause will also be returned in the results. So you get more rows than what is specified in TOP (x).

Let’s do an example 🙂

[sourcecode language=’sql’]
CREATE TABLE FamousTransformers
(ID SMALLINT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
FavouriteColour VARCHAR(50) NOT NULL);

–populate with some data (6 rows to demonstrate this working)
INSERT INTO FamousTransformers(FirstName, LastName, FavouriteColour)
VALUES(‘Clark’,’Kent’,’Blue’);
INSERT INTO FamousTransformers(FirstName, LastName, FavouriteColour)
VALUES(‘Bruce’,’Wayne’,’Black’);
INSERT INTO FamousTransformers(FirstName, LastName, FavouriteColour)
VALUES(‘Peter’,’Parker’,’Red’);
INSERT INTO FamousTransformers(FirstName, LastName, FavouriteColour)
VALUES(‘Optimus’,’Prime’,’Red’);
INSERT INTO FamousTransformers(FirstName, LastName, FavouriteColour)
VALUES(‘David’,’Banner’,’Green’);
INSERT INTO FamousTransformers(FirstName, LastName, FavouriteColour)
VALUES(‘Rodimus’,’Prime’,’Orange’);

–select using normal TOP(x)

SELECT TOP (5) FirstName, LastName, FavouriteColour
FROM FamousTransformers
ORDER BY FavouriteColour
[/sourcecode]
Returns….

FirstName            LastName             FavouriteColour
-------------------- -------------------- --------------------
Bruce                Wayne                Black
Clark                Kent                 Blue
David                Banner               Green
Rodimus              Prime                Orange
Optimus              Prime                Red

(5 row(s) affected)

[sourcecode language=’sql’]
–now execute using WITH TIES
SELECT TOP (5) WITH TIES FirstName, LastName, FavouriteColour
FROM FamousTransformers
ORDER BY FavouriteColour
[/sourcecode]

FirstName            LastName             FavouriteColour
-------------------- -------------------- --------------------
Bruce                Wayne                Black
Clark                Kent                 Blue
David                Banner               Green
Rodimus              Prime                Orange
Peter                Parker               Red
Optimus              Prime                Red

(6 row(s) affected)

So you can see the extra row appearing there where “Optimus Prime” is displayed because he has the same favourite colour as “Peter Parker”. The FavouriteColour column is the key here as defined in the ORDER BY statement.

Actual use cases for SELECT TOP WITH TIES

I can think of one straight off the top of my head. If you had a league table of top performing sales people and wanted to bring back the top three performers, what if two sales people were tied for 3rd and 4th place on revenue? No way to differentiate them, they should both be returned in the results and share 3rd place.

And finally….

You must use ORDER BY and TOP clauses together if you want to use WITH TIES.

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server, t-sql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Humberto says

    August 2, 2016 at 10:07 pm

    Hi Andy,

    You have really nice posts. They’re helping me to improve my career as DBA. I wanted to make an out-of-context question, what font are you using for the T-SQL code and its results? It’s really cool and would like to use it. I’ve been looking around and monospace is what I found. Thanks for sharing.

    Reply
    • Andy Hayes says

      August 7, 2016 at 7:05 pm

      Hey Humberto, thanks for your comments. I’m glad you are finding the posts here useful.

      To be honest I don’t know what font is being used there as its produced by the formatting plugin which I use on the site. It will be somewhere in the CSS but I am not sure where! 🙂

      Reply
    • Ali Khan says

      August 4, 2021 at 2:56 pm

      It looks like Time New Roman

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©