Delete Date that is Older 1

[Compkitty]

I want to be able to find which date is older between two rows w/ the same data.. Except the date(s).. One should be odler than the other and I want to be able to delete the one that is older... I'm not sure of a date function to do that...

 

[vongrunt]

This probably can be easily done with MAX() aggregate. Post sample data and results you want.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Compkitty]

SAMPLE DATA

460 561 522 A 7635 FL E 2005-05-13 00:00:0
460 561 522 A 7635 FL M 2005-05-14 00:00:00

SHOULD DELETE The (E) Record.. and leave
460 561 522 A 7635 FL M 2005-05-14 00:00:00

However, I have a table that has 51 records that are duped like this..

 

[micha123]

delete table1
from table1 t1
where DateField < (select max(DateField)
                   from table1 t2
                   where t1.fld1 = t2.fld2
                     and t1.fld2 = t2.fld2
                     and...)

The only problem is that if you have 2 rows exactly the same (with the same date) and they are the newest ones - this woul not delete them. Do you have a case like this?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[JamesLean]

Something like this should do it:

DELETE table_name
FROM table_name t
WHERE date_col <> (
    SELECT MIN(date_col)
    FROM table_name
    WHERE col1 = t1.col1
      AND col2 = t1.col2
      AND col3 = t1.col3
      ...
  )

--James

 

[JamesLean]

Sorry, I should have used the MAX function in that subquery obviously...

--James

 

[micha123]

Hey, James...
Bit you on this one...



"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[JamesLean]

Slow hands at the end of the day... ;-)

--James

 

[Compkitty]

You guys have been SOOOO helpful and i'm learning sooo much that I (obviously) didn't know or wasn't even thinking of...

 

[micha123]

Hey, Compkitty - thanks for the star!

It's OK, James - I am in the north - so this is my late morning... just kidding... and since you advanced me when I just woke up... --->

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.