SQL Server with ties statements

The WITH TIES statement is a statement that is used in conjunction with the top and order by statements; We will encounter such a situation in the actual query process, such as the query exam for the top three student information, found that there is a third case, if we are only top 3 found tied third student information is not shown, Based on this situation, we need to query with the WITH ties statement. The WITH ties statement acts as a query, based on the last record filtered by top and the other records in the same value as the order by [field].

Example:

1, first we create a simple student score table, and then add 10 test data;

CREATE TABLE Studentscore (

ID int NOT NULL PRIMARY key identity (.),

Studentname varchar (50),

Score int

)

Insert into Studentscore (Studentname,score) VALUES

(' Name1 ', 99),

(' Name2 ', 98),

(' Name3 ', 97),

(' Name4 ', 97),

(' Name5 ', 96),

(' Name6 ', 94),

(' Name7 ', 93),

(' Name8 ', 92),

(' Name9 ', 91),

(' Name10 ', 89)

SELECT * FROM Studentscore

--Now we find the top three of the best test scores

Select top Studentscore ORDER BY score Desc

Now that the problem has come, we have found that there are two students in 97, in fact they are tied to the 3rd, but only to show a student information that is divided into 97; now we're going to use the WITH ties statement.

Select Top 3 with ties * from Studentscore ORDER BY score Desc

--We specify the top 3 but output 4 rows of records, first the query results return the top 3 rows based on the order by score, and then return the last row in the TOP3 with the same score value;

--Tips:with Ties statement can only be used with top, order by

--not used in conjunction with top, order by

Select with Ties * from Studentscore

--not used in conjunction with ORDER by

Select Top 3 with ties * from Studentscore

--not used with top

Select with ties * from Studentscore order by score

SQL Server with ties statements