selecting duplicate values with a condition from a mysql table

Question

I have the following table in mysql:

   Key    DI     CI     FD      FA  NM  Valid_from   Valid_to
    0   1224468 123 2012-06-30  3   6   2013-01-23  9999-12-31
    1   1234567 123 2013-12-31  3   10  2014-02-27  2014-03-10
    2   1234567 123 2013-12-31  2   12  2014-03-10  9999-12-31
    3   1234579 123 2013-12-31  3   12  2014-05-15  9999-12-31
    4   1234595 123 2013-12-31  1   12  2014-06-30  9999-12-31
    5   122469  123 2015-11-11  1   6   2015-11-11  9999-12-31
    6   1224470 123 2015-11-11  2   12  2015-11-11  9999-12-31
    7   1224471 123 2015-11-11  3   15  2015-11-11  9999-12-31
    8   1224472 123 2015-11-10  2   13  2015-11-10  9999-12-31
    9   1224473 123 2015-11-10  3   12  2015-11-10  9999-12-31

If there are records which has the same "FD", I need to get the ones which 's "FA" is "1", if exists.

Basically, I want this output.

Key   DI     CI     FD      FA  NM  Valid_from   Valid_to
 0  1224468 123 2012-06-30  3   6   2013-01-23  9999-12-31
 4  1234595 123 2013-12-31  1   12  2014-06-30  9999-12-31
 5  122469  123 2015-11-11  1   6   2015-11-11  9999-12-31
 8  1224472 123 2015-11-10  2   13  2015-11-10  9999-12-31
 9  1224473 123 2015-11-10  3   12  2015-11-10  9999-12-31

It looks a complicated query, and I couldn't manage to do it.

How can I do it?

Thanks

I don't understand you Code-Monk. If I would have the output, why would I ask a question here? — yusuf
– yusuf, Commented Nov 12, 2015 at 10:52
I mean to say, what rows you want to extract from the data you have in your question. This will be helpful for us to provide correct solution to you and for future visitors if they get the similar problem :) — Abhishek
– Abhishek, Commented Nov 12, 2015 at 10:55

AntDC · Accepted Answer · 2015-11-12 11:14:14Z

1

Looks like a job for ... GROUP BY FD ... HAVING COUNT(FD) > 1

I don't see why you'd want 2012-06-30 in your results? I thought you only wanted ones where there is an FA of 1?

answered Nov 12, 2015 at 11:14

AntDC

1,93715 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek · Accepted Answer · 2015-11-12 11:16:28Z

1

Try out following query:

select * from tbl group by fd having count(*)=1
union all
select t1.* from tbl t1 inner join (
    select max(`key`) as `key` from tbl where fa=1 group by fd
) t2 on t1.`key`=t2.`key` group by FD;

answered Nov 12, 2015 at 11:16

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

1 Comment

yusuf Over a year ago

Thank you for your answer Code-Monk. The thing I exactly need is; for instance, there are duplicate values, but any of them doesn't have any "fa = 1". in that case, it is going to write all the duplicates. How I can manage it? I'm going to edit question. Can you look at it?

Subin Chalil · Accepted Answer · 2015-11-12 17:05:01Z

1

 SELECT T1.*
    FROM table_name T1 LEFT JOIN
     (SELECT * FROM table_name GROUP BY `FD` HAVING COUNT(*)>1 ) T2 ON T1.`FD`=T2.`FD` AND T1.`FA` <>1
WHERE T2.`FD` IS NULL

Can you check this. Hope this helps.

edited Nov 12, 2015 at 17:05

answered Nov 12, 2015 at 12:49

Subin Chalil

3,7082 gold badges31 silver badges44 bronze badges

3 Comments

Abhishek Over a year ago

Only Showing duplicate records of key 4 and 5 and renaming rows are not extracting as per the requirement of OP.

Subin Chalil Over a year ago

WHERE T2.`FA` IS NULL OR T1.`FA` = 1 is was AND, I HAVE EDITED

yusuf Over a year ago

Thank you for your help Subin. There are two errors when I run your script. First, I have seen 4th row as 3 times, 5th row as 2 times, and I didn't see the 0th, 8th and 9th rows.

Collectives™ on Stack Overflow

selecting duplicate values with a condition from a mysql table

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related