select double entrys sorted by unixtime

DkR3 · #1 (**permalink**) 02-09-06, 21:12

hi @all i have a big problem whit my db than i have double entrys on it.

so i select all double entrys whit:

SELECT DISTINCT id, unixtime, ab, cd, de
FROM db
GROUP BY ab
HAVING count( ab ) >1

now i have all dubbel entry
ok thats works but the fault is i have select the oldes entry to delete but i want to delete not the oldest double entrys i wll delete the newest and the oldest let stay.

now my question give it a way to sort on the select the newst entry and not the oldest

i hoppe any1 understand what i mean and can help me

thx
and sorry for my bad englisch

r937 · #2 (**permalink**) 02-11-06, 08:11

your english is pretty good

here is one method (there are others) --

step 1 -- BACK UP YOUR DATA FIRST

step 2 -- create a table of the rows to keep

Code:

create table keepers
select one.id
     , one.unixtime
     , one.ab
     , one.cd
     , one.de
  from db as one
inner
  join db as two
    on one.ab = two.ab
group
    by one.id
     , one.unixtime
     , one.ab
     , one.cd
     , one.de
having one.unixtime = max(two.unixtime)

step 3 -- drop table db

step 4 -- rename table keepers to db

DkR3 · #3 (**permalink**) 02-11-06, 16:25

hi man big big thx this works perfect.

thx

Internet Services

Web Development

Digital Marketing

Consumer Tech