What is the use of WITH TIES keyword in SELECT sta

问题:

SELECT TOP 5 WITH TIES EmpNumber,EmpName 
FROM Employee 
Order By EmpNumber DESC

This above query return more than five result, What is the use of "With Ties" keyword SQL Queries.

回答1:

From TOP (Transact-SQL)

Used when you want to return two or more rows that tie for last place in the limited results set.

Note the example

SQL Fiddle DEMO

We have a table with 6 entires 1 to 4 and 5 twice.

Running

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID;

returns 6 rows, as the last row is tied (exists more than once.)

Where as

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID DESC;

returns only 5 rows, as the last row (2 in this case) exists only once.

回答2:

Check this query and will be more clear.

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID;

RESULT:- 1 2 3 4 5 5

SELECT TOP 5 *
FROM MyTable 
ORDER BY ID;

RESULT:- 1 2 3 4 5

回答3:

According to BOL “WITH TIES specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. “

Refer this link This may helpCode project

link 2

link 3