homepage Welcome to WebmasterWorld Guest from 54.196.225.45
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Help with DELETE statement
Jakotsu




msg:3355332
 3:19 am on Jun 1, 2007 (gmt 0)

Hi, I have a table in which items are added with a timestamp constantly. I need to delete * elements and leave on the table only the most recent 50 items.

How can I do that in a single and efficient statement? Thanks!

 

FalseDawn




msg:3356295
 10:54 pm on Jun 1, 2007 (gmt 0)

DELETE FROM table T1 WHERE timestamp_col<=(SELECT MAX(timestamp_col) FROM table T2 WHERE (SELECT COUNT(*) FROM table T3 WHERE T3.timestamp_col<=T2.timestamp_col<=50))

May or may not work (probably won't as it's off the top of my head...)

Or maybe something like
DELETE FROM table WHERE timestamp_col NOT IN (SELECT timestamp_col FROM table ORDER BY timstamp_col DESC LIMIT 50)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved