Remove all duplicated records from a resultset(remove both)

Question

I have a result set in generated as CTE using Union that contains duplicate records. as in image below:

enter image description here

And the query is:

WITH CTE  (StartTime ,EndTime )
AS
(
SELECT StartTime ,EndTime, Null as Exclude, SupplierId FROM cms.TimeSlotMaster
WHERE Monday = 1 AND SupplierID IS NULL

UNION

SELECT StartTime ,EndTime FROM cms.TimeSlotOverRider
WHERE SupplierID IS NULL
AND StartDate <= cast(GETDATE() as DATE) AND EndDate >= cast(GETDATE() as DATE)
)

Now I am trying to remove the duplicate results from this result set at all. So finally the results set should be only 2 rows. So it should look like below:

enter image description here

Any help would be appreciated. Thanks.

For more information the first result set is generated using below CTE

Duplicate of http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — PKirby, Jul 06 '15 at 09:06

score 1 · Accepted Answer · answered Jul 06 '15 at 09:14

1

You can use NOT EXISTS:

SELECT t.*
FROM dbo.TableName t
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.TableName t2
    WHERE t. ID <> t2.ID
     AND  t.StartTime = t2.StartTime 
     AND  t.EndTime   = t2.EndTime
)

or - if you don't have a primary key in this table:

WITH CTE AS
(
    SELECT t.*, cnt = COUNT(*) OVER (PARTITION BY StartTime, EndTime)
    FROM dbo.TableName t
)
SELECT StartTime, EndTime 
FROM CTE 
WHERE cnt = 1

answered Jul 06 '15 at 09:14

Tim Schmelter

450,073
74
686
939

this result set is generated using union. So I dont have a primary Id kind of column in result set. – Language Lassi Jul 06 '15 at 09:18
Okay let me try the second one. – Language Lassi Jul 06 '15 at 09:19

Remove all duplicated records from a resultset(remove both)

1 Answers1