Professional Web Applications Themes

Deleting all but the last N records that match some key - MySQL

Consider a table like this: name: VARCHAR(50) time: TIMESTAMP with multiple records per "name". I'd like to delete all but the last N records for each "name". Is this expressable in SQL, or do I have to write an external program and grind through the entries? John Nagle...

  1. #1

    Default Deleting all but the last N records that match some key

    Consider a table like this:

    name: VARCHAR(50)
    time: TIMESTAMP

    with multiple records per "name".

    I'd like to delete all but the last N records for each "name".
    Is this expressable in SQL, or do I have to write
    an external program and grind through the entries?

    John Nagle
    John Guest

  2. #2

    Default Re: Deleting all but the last N records that match some key


    "John Nagle" <com> wrote in message
    news:UX0Rh.1288$news.prodigy.net... 



    If you can capture the identity of the fields that you want to keep ...

    SELECT ID FROM TABLE WHERE....

    You can delete everything else ....

    DELETE FROM TABLE WHERE ID NOT IN
    (
    SELECT ID FROM TABLE WHERE....
    )

    Hope that this helps.



    Sean Guest

  3. #3

    Default Re: Deleting all but the last N records that match some key

    On 5 Apr, 07:48, John Nagle <com> wrote: 

    It depends on how you are defining "last"

    Captain Guest

  4. #4

    Default Re: Deleting all but the last N records that match some key

    On Apr 5, 10:02 am, "Captain Paralytic" <com>
    wrote: 




    >
    > It depends on how you are defining "last"[/ref]

    Yeah, but I'm going to guess that "last" is related to to the
    timestamp somehow.

    This method simply gets a list of valid results. You can use it to
    create a temporary table with which you can then overwrite the
    previous results, or you can use it as a subquery to some kind of
    delete statement:

    untested, probably over complicated, but who knows it might just
    work...

    SELECT * FROM my_table X
    JOIN
    (SELECT A.*, COUNT(B.name) rank FROM my_table A
    LEFT OUTER JOIN my_table B
    ON B.name = A.name
    AND B.time >= A.time
    GROUP BY B.name,B.time)Y
    ON Y.name = X.name AND Y.time = X.time HAVING rank < N;

    Obviously, replace every instance of "my_table" with your table name,
    set "N" to any value you like, reverse ">=" if required, and add an
    ORDER BY clause somewhere if it helps. In the unlikely event of two
    things happening to the same "name" at the same "time" you're on your
    own.

    strawberry Guest

  5. #5

    Default Re: Deleting all but the last N records that match some key

    On 5 Apr, 14:59, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Yeah, but I'm going to guess that "last" is related to to the
    > timestamp somehow.
    >
    > This method simply gets a list of valid results. You can use it to
    > create a temporary table with which you can then overwrite the
    > previous results, or you can use it as a subquery to some kind of
    > delete statement:
    >
    > untested, probably over complicated, but who knows it might just
    > work...
    >
    > SELECT * FROM my_table X
    > JOIN
    > (SELECT A.*, COUNT(B.name) rank FROM my_table A
    > LEFT OUTER JOIN my_table B
    > ON B.name = A.name
    > AND B.time >= A.time
    > GROUP BY B.name,B.time)Y
    > ON Y.name = X.name AND Y.time = X.time HAVING rank < N;
    >
    > Obviously, replace every instance of "my_table" with your table name,
    > set "N" to any value you like, reverse ">=" if required, and add an
    > ORDER BY clause somewhere if it helps. In the unlikely event of two
    > things happening to the same "name" at the same "time" you're on your
    > own.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Oops, I missed the timestamp column!
    (skulks away embarresed...)

    Captain Guest

  6. #6

    Default Re: Deleting all but the last N records that match some key

    Sean napisał(a): 
    >
    >
    >
    > If you can capture the identity of the fields that you want to keep ...
    >
    > SELECT ID FROM TABLE WHERE....
    >
    > You can delete everything else ....
    >
    > DELETE FROM TABLE WHERE ID NOT IN
    > (
    > SELECT ID FROM TABLE WHERE....
    > )
    >[/ref]


    Does it really work?

    "
    In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
    DELETE FROM t WHERE ... (SELECT ... FROM t ...);
    UPDATE t ... WHERE col = (SELECT ... FROM t ...);
    {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

    Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause.
    "


    http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

    -news
    news Guest

  7. #7

    Default Re: Deleting all but the last N records that match some key


    "news" <pl> wrote in message
    news:ev3ip7$64l$internetia.pl... 
    >>
    >>
    >>
    >> If you can capture the identity of the fields that you want to keep ...
    >>
    >> SELECT ID FROM TABLE WHERE....
    >>
    >> You can delete everything else ....
    >>
    >> DELETE FROM TABLE WHERE ID NOT IN
    >> (
    >> SELECT ID FROM TABLE WHERE....
    >> )
    >>[/ref]
    >
    >
    > Does it really work?
    >
    > "
    > In general, you cannot modify a table and select from the same table in a
    > subquery. For example, this limitation applies to statements of the
    > following forms: DELETE FROM t WHERE ... (SELECT ... FROM t ...);
    > UPDATE t ... WHERE col = (SELECT ... FROM t ...);
    > {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
    >
    > Exception: The preceding prohibition does not apply if you are using a
    > subquery for the modified table in the FROM clause.
    > "
    >
    >
    > http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html
    >
    > -news[/ref]



    I am pretty sure that it would work.




    Sean Guest

Similar Threads

  1. Replies: 3
    Last Post: October 14th, 03:44 PM
  2. Deleting duplicate records
    By Dinesh.T.K in forum FileMaker
    Replies: 3
    Last Post: August 13th, 12:00 AM
  3. Deleting records against a list
    By Rick Altman in forum FileMaker
    Replies: 2
    Last Post: July 18th, 03:38 AM
  4. deleting records and keeping them
    By kabir in forum Microsoft Access
    Replies: 1
    Last Post: July 13th, 05:33 PM
  5. deleting selection of records
    By George Mizzell in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 13th, 01:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139