CODEWITHSUNDEEP
mysqlsqljoingroup-byinner-join
meallhour
meallhour

Reputation: 15581

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%' 
;

Upvotes: 1

Views: 54

Answers (3)

user9121639
user9121639

Reputation: 

select host
from table p1
where CAST(p1.date AS DATE) = '2017-12-19' 
group by p1.host
having count(distinct p1.ipaddress) >= 2;

You need:

  • Group by host, so when you count the ip address you get the correct count.
  • Use WHERE clause, and since the date is a datetime, don't use a wildcard.
  • SELECT host only.
  • If you want to select extra columns other than host, you will need an extra query.

Note that: There is something wrong in what you are trying to do. How the condition date like '2011-12-19%' should return the expected results like dates 2011-10-18 22:45:16 and 2011-12-19 21:56:46. I think you are misunderstanding the wild card. You cannot use the wild card this way, you can use the dates operators and conditions since it is a datetime column.

Upvotes: 0

jarlh
jarlh

Reputation: 44766

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?)

Upvotes: 1

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Try something like that;

select t1.host from table t1 inner join
(select p1.ipaddress,max(p1.date) as date from table p1
where cast(p1.date as Date) = '2017-12-19'
group by p1.ipaddress 
having count(*) > 1) t2 
ON t1.ipaddress = t2.ipaddress and t1.date = t2.date

I strongly suggest you, don't compare the date fields as like.

Upvotes: 0

Related Questions