This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The SQL Server TOP clause WITH TIES option is not a new feature, but still many of database developers don’t know about it.
This option is available with the TOP clause since a long time, and this is one of the best TSQL options.
We are using the TOP clause to a SELECT TOP N number of records from a Table, but using TOP WITH TIES option, we can also include the duplicate or same value of records in the result.
The TOP WITH TIES option works based on a defined column in the ORDER BY clause.
Below is a small demonstration of TOP WITH TIES clause:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE tbl_TestTOPClause ( EmpID INT IDENTITY(1,1) ,EmpName VARCHAR(50) ,Age INT ,DepartmentName VARCHAR(50) ,CONSTRAINT pk_tbl_TestTOPClause_EmpID PRIMARY KEY(EmpID) ) GO INSERT INTO tbl_TestTOPClause (EmpName,Age,DepartmentName) VALUES ('Anvesh',28,'Production') ,('Neevan',20,'Sales') ,('Jenny',21,'Account') ,('Jenny',24,'Production') ,('Martin',25,'Inventory') ,('Ruhan',30,'Sales') ,('Peter',33,'Account') ,('Peter',26,'Production') ,('Peter',23,'Inventory') GO |
SELECT TOP records with out WITH TIES option:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT TOP 3 * FROM tbl_TestTOPClause ORDER BY EmpName DESC GO --The Result: EmpID EmpName Age DepartmentName ----------- ---------- ----------- ---------------------- 6 Ruhan 30 Sales 7 Peter 33 Account 8 Peter 26 Production |
SELECT TOP records using WITH TIES option using EmpName in ORDER BY:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT TOP 3 WITH TIES * FROM tbl_TestTOPClause ORDER BY EmpName DESC GO --The Result: Return 4 records because Empname = Peter is a duplicate three times. EmpID EmpName Age DepartmentName ----------- ----------- ----------- ------------------------ 6 Ruhan 30 Sales 7 Peter 33 Account 8 Peter 26 Production 9 Peter 23 Inventory |
