CODEWITHSUNDEEP
mysqlsql
Vivek Sahni
Vivek Sahni

Reputation: 57

Get records group by multiple column mysql

Here is my table structure

comment_file_id  comment_id  attachement_id  source  created_by
   33           203               42          IN        101
   35           203               46          OUT       101
   36           203               42          OUT       101
   37           203               42          OUT       101

i want to get only 3 records only as records associated with attachment_id 42 are duplicates for source OUT , so oi have to exclude those ones. 

    select * 
      from comments 
     where comment_id = 203 
     GROUP 
        BY source
         , created_by 
         , attachement_id ;

but getting errors

Upvotes: 0

Views: 45

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

If you are using MySQL 8+, then ROW_NUMBER is helpful here. We can phrase your query as a union of all records not subject to duplicate constraints, along with the single record not deemed a duplicate:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY comment_file_id) rn
    FROM comments
    WHERE comment_id = 203 AND attachement_id = 42 AND source = 'OUT'
)

SELECT comment_file_id, comment_id, attachement_id, source, created_by
FROM comments
WHERE comment_id = 203 AND (attachement_id <> 42 OR source <> 'OUT')
UNION ALL
SELECT comment_file_id, comment_id, attachement_id, source, created_by
FROM cte
WHERE rn = 1;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use min() 

select min(comment_file_id) as comment_file_id,comment_id,attachement_id,source
,created_by from table
group by comment_id,attachement_id,source
,created_by

Upvotes: 1

Related Questions