Removing Duplicate rows in a table by matching two columns data

Databases
1

Hello,

empno | date

38084 | 2014-02-26 18:22
38084 | 2014-02-26 18:26
38084 | 2013-07-1 18:26
38011 | 2014-02-26 18:59
38011 | 2014-02-26 19:26

now i want to remove any duplicate employee numbers having the same date (yyyy-mm-dd and time can be diffrent).

i know how to remove duplicates, but not getting how to do it for more than one column.

i tried this

SELECT empno,date, COUNT(*) c FROM employees
GROUP BY empno HAVING c > 1
AND date=left(date,10)

but no luck.

2

What is the “field type” (eg, date, varchar, text, etc) of the date field?

3

varchar :slight_smile:

btw, i had put a mistake here in my eample table

38084 | 2013-07-1 18:26
should be
38084 | 2013-07-01 18:26

the date part is always fixed in length

4

hello

5

hi, how ya doin?

SELECT empno
     , LEFT(date,10) 
  FROM employees 
GROUP 
    BY empno
     , LEFT(date,10)
HAVING COUNT(*) > 1
6

Hai r937,
thank you!
now i could not get the delete part to work. I want to remove the duplicates.

delete from employees
WHERE
  employees.empno 
  in 
  (SELECT empno, LEFT( date, 10 )
FROM test
GROUP
BY empno, LEFT( date, 10 )
HAVING COUNT( * ) >1)
7

gee, i’m sorry, i cannot see your error message from here

8 
delete from employees
WHERE
  employees.empno 
  in 
  (SELECT empno, LEFT( date, 10 )
FROM employees
GROUP
BY empno, LEFT( date, 10 )
HAVING COUNT( * ) >1)

it was saying operand missing or something.

9

Operand should contain 1 column(s) - this is the error.

10

do you understand what it’s telling you?

your WHERE clause specifies one column, employees.empno, but your IN subquery produces two

11

undestood r937. then i also tried like this.

DELETE FROM employees
WHERE employees.empno
IN 
(SELECT empno
FROM employees
GROUP
BY empno, LEFT( date, 10 )
HAVING COUNT( * ) >1)

#1093 - You can’t specify target table ‘employees’ for update in FROM clause

:rolleyes:

12

any way i managed to solve the issue ass follows.

SELECT empno
     ,date
  FROM employees
GROUP
    BY empno
     , LEFT(date,10)
HAVING COUNT(*) > [B]0[/B]

It returned all unique rows. i experted the result as csv, then empty the table, imported back the data from csv.

Thank you.

13

you still have your duplicates

HAVING COUNT(*) > 0 will return all rows in the table

14

hai r937

i see duplicate empnos but dates are unique to each.

see the sample data

btw, i had 550 records in the table with many duplicates, that query returned 164 of them

15

yes, you’re right, i was confused for a moment :slight_smile:

16

tx for the confirmation r937 :slight_smile: