Home » SQL

SQL WITH TIES Clause

SQL | WITH TIES Clause: In this tutorial, we are going to learn about the WITH TIES Clause with its usages, syntax and query examples in SQL (Structured Query Language).
Submitted by Abhishek Goel, on April 05, 2020

SQL | WITH TIES Clause

WITH TIES clause is newly added to the oracle 12c software. So, to understand the concept we will take an example.

Before that just have a look at the syntax:

    SELECT TOP (<number_of_rows>) WITH TIES <column_name>/*(for_all_columns)
    FROM <table_name>
    ORDER BY <column_name> <order(ASC/DESC)>;

Here, ASC stands for Ascending order and DESC stands for Descending order.

Example:

Suppose we have 10 runners in the race and we have only 3 prizes to give away 1st, 2nd & 3rd respectively. But the situation arises that two runners finish the race exactly at the same time and are eligible for the 3rd position prize.

To overcome this problem SQL provides us with the clause known as WITH TIES clause.

To have a better understanding let's have the following example.

SNO	NAME	RANK
1	Nitin	First
2	Prakash	Second
3	Parmeet	Third
4	Sunny	Third
5	Hassan	Fourth
6	Rithwik	Fifth

Now, if we want the names of the player having the first three position,

Query:

SELECT TOP(4) WITH TIES sno, name, rank
FROM mytable
ORDER BY rank ASC;

Output

SNO	NAME	RANK
1	Nitin	First
2	Prakash	Second
3	Parmeet	Third
4	Sunny	Third

Note:

• We get the tied line in our yield, just when we utilize the request by the provision in our Select explanation. Assume, if we won't use request by the proviso, and still, we are utilizing with ties statement, at that point we won't get the tied line in our yield and the inquiry carries on the same as if we are utilizing the ONLY condition rather than With Ties proviso.
• If it's not too much trouble ensure that, you run these inquiries in Oracle Database 12c, because Fetch provision is the recently included component in Oracle 12c, additionally With Ties, runs just in Oracle Database 12c, these questions won't run in beneath renditions of 12c like 10g or 11g.