Search code examples
mysqlsqljoingroup-byinner-join

MYSQL getting duplicate values from a table

I have mysql table with three fields

host              ipaddress      date
----              ---------      -----
server1.abc.com   10.1.1.1      2011-10-18 22:45:16
server1           10.1.1.1      2011-12-19 21:56:46
server2           11.1.1.1      2011-12-18 21:56:46
server2.abc.com   11.1.1.1      2011-12-17 21:56:46

Here,
host --> varchar
ipaddress --> varchar
date ---> datetime

I want to select host in the table which have duplicate entries for ip I only want to check duplicate entry for host only if date like '2011-12-19%'

From above table, my result should satisfy condition date like '2011-12-19%'

host              ipaddress      date
----              ---------      -----
server1.abc.com   10.1.1.1      2011-10-18 22:45:16
server1           10.1.1.1      2011-12-19 21:56:46

I have written following query but getting 0 rows

select * from table p1
group by p1.ipaddress having count(*) >= 2
and p1.date like '2017-12-19%' 
;
Solution

  • The sub-query t2 returns ipaddresses that have at least 2 rows, and at least one of the for that specific date. JOIN with t2's result.

    select t1.*
from tablename t1
join (select ipaddress
      from tablename
      group by ipaddress
      having count(*) >= 2
         and count(case when CAST(date AS DATE) = '2017-12-19' then 1 end) > 0) t2
  on t1.ipaddress = t2.ipaddress

    According to ANSI SQL date is a reserved word, so you may have to delimit it. (Back-ticks?)