List duplicate data

Navigation
1

I am suffering with duplicate data in the database and need to list them to check them and delete them. Consider as duplicate data when the value and the date coincide, as in the example below, the values A and C are duplicated. 

A: 2017-08-10 - 10.00
B: 2017-08-10 - 10.01
C: 2017-08-10 - 10.00
D: 2017-08-11 - 10.00

Through a precise query, only A and C appear. 

A: 2017-08-10 - 10.00
C: 2017-08-10 - 10.00

I tried this way, but to no avail: 

SELECT data, valor FROM tabela
WHERE data > '2017-04-17'
HAVING COUNT(valor) > 1

Unfortunately this way the data is grouped. I tried to GROUP BY primary_key unsuccessfully.

Note: MySQL 5.7

    
asked by anonymous 10.08.2017 / 20:44

1 answer

1

See if it is: 

select 
    * 
from 
    table1 a
where 
    ( data, valor ) in 
        ( select 
             data, valor 
          from table1 
              group by data, valor 
          having count(*) > 1 )

Run in SQL Fiddle.

    
10.08.2017 / 21:10
How to change the order of the parameters of the Friendly Url in htaccess? Capture groups where a specific word appears with Regex