Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: select double entrys sorted by unixtime

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    hi man big big thx this works perfect.

    thx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •