CODEWITHSUNDEEP
sqlsql-serverleft-joinunionexists
Need Help
Need Help

Reputation: 1

Stored Procedure that returns duplicate rows with different IDs

I am trying to stop duplicate data being returned. I have a DataId that has unique ids for 3 records that are all the same. Please see the example below

DateId DateMessage Colour DataType MessageType
1 2024-12-10 00:00:57.000 red continual single
2 2024-12-10 00:00:57.000 red continual single
3 2024-12-10 00:00:57.000 red continual single
4 2024-12-10 00:10:57.000 red continual single
5 2024-12-10 00:10:57.000 red continual single
6 2024-12-10 00:10:57.000 red continual single
7 2024-12-10 00:20:57.000 red continual single
8 2024-12-10 00:20:57.000 red continual single
9 2024-12-10 00:20:57.000 red continual single

As you can see the rows are duplicated (aside from the DataId) in duplications of 3. I would ideally want to see something like this instead

DateId DateMessage Colour DataType MessageType
1 2024-12-10 00:00:57.000 red continual single
4 2024-12-10 00:10:57.000 red continual single
7 2024-12-10 00:20:57.000 red continual single

So far I have tried a LEFT JOIN

    SELECT * 
    FROM
      (SELECT DISTINCT 
             [DateMessage]
            ,[Colour]
            ,[DataType]
            ,[MessageType]
       FROM [Data]) d

    LEFT JOIN 

      (SELECT [DateId]
              ,[DateMessage]
              ,[Colour]
              ,[DataType]
              ,[MessageType]
       FROM [Data])

but then the only column that I can join it to is the DataId (which does not exist in the first part of the join. I have also tried UNION (but that requires both select queries to be exactly the same). CROSS APPLY did not work either. And WHERE EXISTS just returned all the results (basically all 9 rows). How would I go about this?

Upvotes: 0

Views: 26

Answers (0)

Related Questions