Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 47

Instead of adding a tiebreaker to the ORDER BY list, you can request to return all ties. For example, 

besides the five rows that you get back from the query in Listing 2-5, you can ask to return all other 

rows from the table that have the same sort value (order date, in this case) as the last one found (May 

5, 2008, in this case). You achieve this by adding the WITH TIES option, as shown in the following query.

SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid 

FROM Sales.Orders 

ORDER BY orderdate DESC;

This query returns the following output.

orderid     orderdate                    custid      empid 

----------- ---------------------------- ----------- ----------- 

11077       2008-05-06 00:00:00.000      65          1 

11076       2008-05-06 00:00:00.000      9           4 

11075       2008-05-06 00:00:00.000      68          8 

11074       2008-05-06 00:00:00.000      73          7 

11073       2008-05-05 00:00:00.000      58          2 

11072       2008-05-05 00:00:00.000      20          4 

11071       2008-05-05 00:00:00.000      46          1 

11070       2008-05-05 00:00:00.000      44          2 

 

(8 row(s) affected)

Notice that the output has eight rows, even though you specified TOP (5). SQL Server first returned 

the TOP (5) rows based on orderdate DESC ordering, and also all other rows from the table that had 

the same orderdate value as in the last of the five rows that was accessed.