Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Talking Unanswered: Slective Duplicate Record Delete/Identify Question

    HTML Code:
    HI, I have a table similiar to this:
    
    Division     Type     Section     Location          ModificationDate
    -------     -----     -------     --------          ---------------
    3             4           2             Los Angeles      2/1/05
    3             4           2             New York         2/4/05
    4             5           1             Los Angeles      2/4/05
    3             4           2             Seattle            2/7/05
    4             5           1             Dallas              2/6/05
    3             4           4             London            2/3/05
    
    I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like:
    
    Division     Type     Section     Location          ModificationDate
    -------     -----     -------     --------          ---------------
    3             4           2             Seattle            2/7/05
    3             4           4             London            2/3/05
    4             5           1             Dallas              2/6/05
    
    Does anyone have idea how I would do something like this? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Division
         , Type
         , Section
         , Location
         , ModificationDate
      from yourtable as foo
     where ModificationDate
         = ( select max(ModificationDate)  
               from yourtable
              where Division = foo.Division
                and Type     = foo.Type
                and Section  = foo.Section )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    3
    Do I need to specifically selectv all the columns in the top level selct staement below?? ... or can I do "select *" ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would always list the columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    3
    thank you r937

Posting Permissions

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