How to remove duplicate rows in SELECT request with 5 fields

Question

I need to select 5 fields in a SELECT statement.

2 of these 5 fields are different, so when I try a SELECT with only these 2 fields I obtain the result I want (I put just a DISTINCT in this request).

The 3 others fields are different, so when I add them to the SELECT statement, the DISTINCT isn't enough. I get duplicate rows.

The aim is to obtain the result of my request with the two fields and then add to the result row the top 1 for instance of the other fields. It is possible to do that?

This is the request :

      -- Here Id, Description, and Image are differents and give duplicate rows
    SELECT DISTINCT Title, GroupingId, Id, Description, Image
        FROM dbo.AllContent     
        WHERE Title like '%'+'harr'+'%'
        AND Rating <> 18

--result 89 rows...

    -- No problems withs these fields   
    SELECT Title,GroupingId
        FROM dbo.AllContent     
        WHERE Title like '%'+'harr'+'%'
        AND Rating <> 18

--result 28 rows...

It is possible to use GROUP BY statement on the 5 fields ?

I add the output with the first request (5 fields)

Title   |  Id   |  GroupingId   |  Description  |  Image
Title1     1           1           Description1    1.jpg
Title1     2           1           Description2    2.jpg
Title1     3           1           Description3    3.jpg
Title4     4           4           Description4    4.jpg
Title5     5           5           Description5    5.jpg
Title5     6           5           Description6    6.jpg

Then with the request with 2 fields:

Title   |  GroupingId
Title 1        1     
Title 4        4
Title 5        5

What I need :

Title   |   Id  | GroupingId   |  Description   |  Image
Title 1     1         1          Description 1       1.jpg
Title 4     4         4          Description 4       4.jpg
Title 5     5         5          Description 5       5.jpg

From the example you added, it looks like your ID, Description, Image columns have unique values. You can use the where clause to just get the IDs you need in the result set...for e.g. where ID in (1,4,5) to get the result you showed us above. — FutbolFan, Apr 20 '15 at 13:52

score 1 · Accepted Answer · answered Apr 20 '15 at 13:48

1

I don't really know what you want in your where clause so you can add that. But this should do what you need.

SELECT  Title,
        MIN(ID) AS ID,
        GroupingID,
        MIN([Description]) AS [Description],
        MIN([Image]) AS [Image]
FROM dbo.AllContent 
GROUP BY Title,GroupingID

answered Apr 20 '15 at 13:48

Stephan

5,891
1
16
24

1

This respond exactly to my questions, with your request i have not duplicate rows ! To answers to your question my clause where use other fields in the table AllContent and the Title must contain the letters 'harr' – Julien698 Apr 20 '15 at 13:57

score 0 · Answer 2 · answered Apr 20 '15 at 13:32

You can choose an arbitrary row from each 2-column group by using row_number():

SELECT Title, GroupingId, Id, Description, Image
FROM (SELECT ac.*,
             r
      FROM dbo.AllContent ac     
      WHERE ac.Title like '%'+'harr'+'%' AND Rating <> 18
     ) ac
WHERE seqnum = 1

score 0 · Answer 3 · answered Apr 20 '15 at 13:56

0

Based on the example provided above, this should work out for you.

    SELECT Title, GroupingId, Id, Description, Image
    FROM dbo.AllContent     
    WHERE Title like '%'+'harr'+'%'
    AND Rating <> 18
    AND Id in (1,4,5)

answered Apr 20 '15 at 13:56

FutbolFan

13,235
3
23
35

score -1 · Answer 4 · edited May 23 '17 at 11:51

-1

This one is with 100 upvotes. I am in love with CTE after using this. Why dont you try this !!

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

edited May 23 '17 at 11:51

Community

1
1

answered Apr 20 '15 at 13:59

Language Lassi

2,520
21
24

How to remove duplicate rows in SELECT request with 5 fields

4 Answers4