WITH TIES Clause

Introductions 

If we need TOP n records from a table with ORDER BY a columns we are using this syntax

SELECT    TOP n [Column Name 1], [Column Name 2] ...

FROM      [Table Name]

ORDER BY  [Column Name]

But we have some problem with this.

So What's the Problem

If the Table have the multiple records with same value. Only one value is selected in this case.

To understand it properly let's take an example

Step-1 [ Create The Base Table ]

-- The Base Table

IF OBJECT_ID('tbl_Example') IS NOT NULL

   BEGIN

     DROP TABLE tbl_Example;

   END

GO  

CREATE TABLE tbl_Example

       (CategoryName   VARCHAR(50)   NOT NULL,

        CategoryValue  DECIMAL(10,0) NOT NULL);

GO

Step-2 [ Insert Some records ]

INSERT INTO tbl_Example

       (CategoryName,  CategoryValue)

VALUES ('CAT-1',  10),             

       ('CAT-2',  10),

       ('CAT-3',  10),

       ('CAT-4',  20),

       ('CAT-5',  20),

       ('CAT-6',  30),

       ('CAT-7',  30),

       ('CAT-8',  30),

       ('CAT-9',  30),

       ('CAT-10', 30),

       ('CAT-11', 40)

Step-3 [ Now make the Query with TOP 1 ]

SELECT  TOP 1 CategoryName,  CategoryValue

FROM    tbl_Example 

ORDER BY CategoryValue

Output:

CategoryName          CategoryValue

CAT-2                         10

If we look at the output only one record is selected. But Category Value 10 is exists for 3 records in the base table.

How to Solve it

To solve this problem we use WITH TIES after TOP n clause.

SELECT  TOP 1 WITH TIES CategoryName,  CategoryValue

FROM    tbl_Example          

ORDER BY CategoryValue

Now look at the output:

CategoryName          CategoryValue

CAT-1                         10

CAT-2                         10

CAT-3                         10

Now all the three records will display.

Caution

WITH TIES is used with ORDER BY clause. If we don't use the ORDER BY clause an error is generated.

Msg 1062, Level 15, State 1, Line 2

The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.

Hope you like it.

Posted by: MR. JOYDEEP DAS