Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

MySQL: How can I delete all rows after the first 50 returned?

Basically I want to delete all rows after the first 50 returned.

So here's an example, here's a query, all of the results I want to keep, any row after this I'd like to have deleted:
SELECT * FROM game_shouts WHERE game_id=478 ORDER BY time DESC LIMIT 50

So any row past row 50 that has a game_id of 478 I'd like to delete with a separate query, how would I do this?
0
davideo7
Asked:
davideo7
  • 2
4 Solutions
 
Patrick MatthewsCommented:
May be slow :)

DELETE FROM game_shouts
WHERE time IN
    (SELECT time
    FROM game_shouts
    WHERE game_id=478
    ORDER BY time DESC
    LIMIT 50)
0
 
Patrick MatthewsCommented:
I may have misread the question.  Perhaps you meant:


DELETE FROM game_shouts
WHERE time NOT IN
    (SELECT time
    FROM game_shouts
    WHERE game_id=478
    ORDER BY time DESC
    LIMIT 50)

Open in new window

0
 
dolomitedaveCommented:
you could use LIMIT and OFFSET

SELECT * FROM game_shouts WHERE game_id=478 ORDER BY time DESC LIMIT 9999 OFFSET 50;

only problem is limit has to be higher than all your records
0
 
wnaudeCommented:
read them into a new table delete the old table copy back to old table
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now