Text copied!
SQL WITH TIES
In SQL, the WITH TIES is used in conjunction with the ORDER BY clause. When you use WITH TIES, it means you want to get more results than you asked for if there are any "ties" with the last result.
For example : if you ask for the top 5 salaries and two people have the same fifth-highest salary, WITH TIES will include those two people in your result, giving you 6 rows.
Here's the syntax for using WITH TIES :
SELECT TOP n WITH TIES column1, column2, column3 ...
FROM table_name
ORDER BY column_name DESC;
• n : It is the number of rows you want to retrieve. i.e. 5 rows
• column1, column2, column3, ... : These are the columns you want to retrieve.
• table_name : It is the name of the table you're querying.
• column_name : It is the column by which you're ordering the result set.
Here's an example of how to use WITH TIES in a SQL query :
1. Let assume you have a table called Employees with columns like Employee_id, Employee_Name, Gender, Salary, and Joining_Date.
2. If you've noticed, we have three employees earning the same salary of 4000, which is the highest in our records. Let's retrieve the top 2 employees with the highest salary using the TOP clause along with WITH TIES.
3. Run below SQL statement :
SELECT
TOP 2 WITH TIES
Employee_id, Employee_Name, Gender, Salary, Joining_Date
FROM [Employees]
ORDER BY Salary DESC;
4.The previous query brought back 3 rows instead of just the top 2 because we included the WITH TIES keywords with the TOP clause. WITH TIES includes additional rows when multiple rows share the same value. In our case, multiple employees share the same salary of 4000, resulting in a tie.
5. Now, let's execute it without using WITH TIES and see what output we receive. Run below SQL statement :
SELECT
TOP 2
Employee_id, Employee_Name, Gender, Salary, Joining_Date
FROM [Employees]
ORDER BY Salary DESC;
6. You can confirm that when we query without the WITH TIES, we receive only 2 rows.
Hence, you have successfully demonstrated the use of the WITH TIES. Thank you.