Friday 11 January 2013

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

10 comments: