Delete all rows except first N from a table having single column









up vote
5
down vote

favorite
3












I need a single query. Delete all rows from the table except the top N rows. The table has only one column. Like,



|friends_name|
==============
| Arunji |
| Roshit |
| Misbahu |
| etc... |


This column may contain repeated names as well.



  • Contains repeated names


  • Only one column.










share|improve this question





















  • You don't have an id field ??
    – Imane Fateh
    Jun 25 '13 at 8:19










  • @ImaneFateh No.. no id field.
    – NaaN
    Jun 25 '13 at 8:20










  • Can you reverse the order and put the top N as last N ? if so you can use limit...
    – Imane Fateh
    Jun 25 '13 at 8:27














up vote
5
down vote

favorite
3












I need a single query. Delete all rows from the table except the top N rows. The table has only one column. Like,



|friends_name|
==============
| Arunji |
| Roshit |
| Misbahu |
| etc... |


This column may contain repeated names as well.



  • Contains repeated names


  • Only one column.










share|improve this question





















  • You don't have an id field ??
    – Imane Fateh
    Jun 25 '13 at 8:19










  • @ImaneFateh No.. no id field.
    – NaaN
    Jun 25 '13 at 8:20










  • Can you reverse the order and put the top N as last N ? if so you can use limit...
    – Imane Fateh
    Jun 25 '13 at 8:27












up vote
5
down vote

favorite
3









up vote
5
down vote

favorite
3






3





I need a single query. Delete all rows from the table except the top N rows. The table has only one column. Like,



|friends_name|
==============
| Arunji |
| Roshit |
| Misbahu |
| etc... |


This column may contain repeated names as well.



  • Contains repeated names


  • Only one column.










share|improve this question













I need a single query. Delete all rows from the table except the top N rows. The table has only one column. Like,



|friends_name|
==============
| Arunji |
| Roshit |
| Misbahu |
| etc... |


This column may contain repeated names as well.



  • Contains repeated names


  • Only one column.







mysql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jun 25 '13 at 8:17









NaaN

1,9711663117




1,9711663117











  • You don't have an id field ??
    – Imane Fateh
    Jun 25 '13 at 8:19










  • @ImaneFateh No.. no id field.
    – NaaN
    Jun 25 '13 at 8:20










  • Can you reverse the order and put the top N as last N ? if so you can use limit...
    – Imane Fateh
    Jun 25 '13 at 8:27
















  • You don't have an id field ??
    – Imane Fateh
    Jun 25 '13 at 8:19










  • @ImaneFateh No.. no id field.
    – NaaN
    Jun 25 '13 at 8:20










  • Can you reverse the order and put the top N as last N ? if so you can use limit...
    – Imane Fateh
    Jun 25 '13 at 8:27















You don't have an id field ??
– Imane Fateh
Jun 25 '13 at 8:19




You don't have an id field ??
– Imane Fateh
Jun 25 '13 at 8:19












@ImaneFateh No.. no id field.
– NaaN
Jun 25 '13 at 8:20




@ImaneFateh No.. no id field.
– NaaN
Jun 25 '13 at 8:20












Can you reverse the order and put the top N as last N ? if so you can use limit...
– Imane Fateh
Jun 25 '13 at 8:27




Can you reverse the order and put the top N as last N ? if so you can use limit...
– Imane Fateh
Jun 25 '13 at 8:27












3 Answers
3






active

oldest

votes

















up vote
10
down vote



accepted










If you can order your records by friends_name, and if there are no duplicates, you could use this:



DELETE FROM names
WHERE
friends_name NOT IN (
SELECT * FROM (
SELECT friends_name
FROM names
ORDER BY friends_name
LIMIT 10) s
)


Please see fiddle here.



Or you can use this:



DELETE FROM names ORDER BY friends_name DESC
LIMIT total_records-10


where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.






share|improve this answer




















  • Can't able to see the fiddle.
    – NaaN
    Jun 25 '13 at 8:40

















up vote
3
down vote













If you don't have an id field, i suppose you use an alphabetic order.



MYSQL



DELETE FROM friends 
WHERE friends_name
NOT IN (
SELECT * FROM (
SELECT friends_name
FROM friends
ORDER BY friends_name ASC
LIMIT 10) r
)


You delete all rows exept the 10 firsts (alphabetic order)






share|improve this answer





























    up vote
    0
    down vote













    I just wanted to follow up on this relatively old question because the existing answers don't capture the requirement and/or are incorrect. The question states the names can be repeated, but only the top N must be preserved. Other answers will delete incorrect rows and/or incorrect number of them.



    For example, if we have this table:



    |friends_name|
    ==============
    | Arunji |
    | Roshit |
    | Misbahu |
    | Misbahu |
    | Roshit |
    | Misbahu |
    | Rohan |


    And we want to delete all but top 3 rows (N = 3), the expected result would be:



    |friends_name|
    ==============
    | Arunji |
    | Roshit |
    | Misbahu |


    The DELETE statement from the currently selected answer will result in:



    |friends_name|
    ==============
    | Arunji |
    | Misbahu |
    | Misbahu |
    | Misbahu |


    See this sqlfiddle. The reason for this is that it first sorts names alphabetically, then takes top 3, then deletes all that don't equal that. But since they are sorted by name they may not be the top 3 we want, and there's no guarantee that we'll end up with only 3.



    In the absence of unique indexes and other fields to determine what "top N" means, we go by the order returned by the database. We could be tempted to do something like this (substitute 99999 with however high number):



    DELETE FROM names LIMIT 99999 OFFSET 3


    But according to MySQL docs, while the DELETE supports the LIMIT clause, it does not support OFFSET. So, doing this in a single query, as requested, does not seem to be possible; we must perform the steps manually.



    Solution 1 - temporary table to hold top 3



    CREATE TEMPORARY TABLE temp_names LIKE names;
    INSERT INTO temp_names SELECT * FROM names LIMIT 3;
    DELETE FROM names;
    INSERT INTO names SELECT * FROM temp_names;


    Here's the sqlfiddle for reference.



    Solution 2 - new table with rename



    CREATE TABLE new_names LIKE names;
    INSERT INTO new_names SELECT * FROM names LIMIT 3;
    RENAME TABLE names TO old_names, new_names TO names;
    DROP TABLE old_names;


    Here's the sqlfiddle for this one.



    In either case, we end up with top 3 rows in our original table:



    |friends_name|
    ==============
    | Arunji |
    | Roshit |
    | Misbahu |





    share|improve this answer




















      Your Answer






      StackExchange.ifUsing("editor", function ()
      StackExchange.using("externalEditor", function ()
      StackExchange.using("snippets", function ()
      StackExchange.snippets.init();
      );
      );
      , "code-snippets");

      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "1"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













       

      draft saved


      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f17292375%2fdelete-all-rows-except-first-n-from-a-table-having-single-column%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      10
      down vote



      accepted










      If you can order your records by friends_name, and if there are no duplicates, you could use this:



      DELETE FROM names
      WHERE
      friends_name NOT IN (
      SELECT * FROM (
      SELECT friends_name
      FROM names
      ORDER BY friends_name
      LIMIT 10) s
      )


      Please see fiddle here.



      Or you can use this:



      DELETE FROM names ORDER BY friends_name DESC
      LIMIT total_records-10


      where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.






      share|improve this answer




















      • Can't able to see the fiddle.
        – NaaN
        Jun 25 '13 at 8:40














      up vote
      10
      down vote



      accepted










      If you can order your records by friends_name, and if there are no duplicates, you could use this:



      DELETE FROM names
      WHERE
      friends_name NOT IN (
      SELECT * FROM (
      SELECT friends_name
      FROM names
      ORDER BY friends_name
      LIMIT 10) s
      )


      Please see fiddle here.



      Or you can use this:



      DELETE FROM names ORDER BY friends_name DESC
      LIMIT total_records-10


      where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.






      share|improve this answer




















      • Can't able to see the fiddle.
        – NaaN
        Jun 25 '13 at 8:40












      up vote
      10
      down vote



      accepted







      up vote
      10
      down vote



      accepted






      If you can order your records by friends_name, and if there are no duplicates, you could use this:



      DELETE FROM names
      WHERE
      friends_name NOT IN (
      SELECT * FROM (
      SELECT friends_name
      FROM names
      ORDER BY friends_name
      LIMIT 10) s
      )


      Please see fiddle here.



      Or you can use this:



      DELETE FROM names ORDER BY friends_name DESC
      LIMIT total_records-10


      where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.






      share|improve this answer












      If you can order your records by friends_name, and if there are no duplicates, you could use this:



      DELETE FROM names
      WHERE
      friends_name NOT IN (
      SELECT * FROM (
      SELECT friends_name
      FROM names
      ORDER BY friends_name
      LIMIT 10) s
      )


      Please see fiddle here.



      Or you can use this:



      DELETE FROM names ORDER BY friends_name DESC
      LIMIT total_records-10


      where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.







      share|improve this answer












      share|improve this answer



      share|improve this answer










      answered Jun 25 '13 at 8:30









      fthiella

      39k126287




      39k126287











      • Can't able to see the fiddle.
        – NaaN
        Jun 25 '13 at 8:40
















      • Can't able to see the fiddle.
        – NaaN
        Jun 25 '13 at 8:40















      Can't able to see the fiddle.
      – NaaN
      Jun 25 '13 at 8:40




      Can't able to see the fiddle.
      – NaaN
      Jun 25 '13 at 8:40












      up vote
      3
      down vote













      If you don't have an id field, i suppose you use an alphabetic order.



      MYSQL



      DELETE FROM friends 
      WHERE friends_name
      NOT IN (
      SELECT * FROM (
      SELECT friends_name
      FROM friends
      ORDER BY friends_name ASC
      LIMIT 10) r
      )


      You delete all rows exept the 10 firsts (alphabetic order)






      share|improve this answer


























        up vote
        3
        down vote













        If you don't have an id field, i suppose you use an alphabetic order.



        MYSQL



        DELETE FROM friends 
        WHERE friends_name
        NOT IN (
        SELECT * FROM (
        SELECT friends_name
        FROM friends
        ORDER BY friends_name ASC
        LIMIT 10) r
        )


        You delete all rows exept the 10 firsts (alphabetic order)






        share|improve this answer
























          up vote
          3
          down vote










          up vote
          3
          down vote









          If you don't have an id field, i suppose you use an alphabetic order.



          MYSQL



          DELETE FROM friends 
          WHERE friends_name
          NOT IN (
          SELECT * FROM (
          SELECT friends_name
          FROM friends
          ORDER BY friends_name ASC
          LIMIT 10) r
          )


          You delete all rows exept the 10 firsts (alphabetic order)






          share|improve this answer














          If you don't have an id field, i suppose you use an alphabetic order.



          MYSQL



          DELETE FROM friends 
          WHERE friends_name
          NOT IN (
          SELECT * FROM (
          SELECT friends_name
          FROM friends
          ORDER BY friends_name ASC
          LIMIT 10) r
          )


          You delete all rows exept the 10 firsts (alphabetic order)







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jun 25 '13 at 8:44

























          answered Jun 25 '13 at 8:29









          Edgar

          1486




          1486




















              up vote
              0
              down vote













              I just wanted to follow up on this relatively old question because the existing answers don't capture the requirement and/or are incorrect. The question states the names can be repeated, but only the top N must be preserved. Other answers will delete incorrect rows and/or incorrect number of them.



              For example, if we have this table:



              |friends_name|
              ==============
              | Arunji |
              | Roshit |
              | Misbahu |
              | Misbahu |
              | Roshit |
              | Misbahu |
              | Rohan |


              And we want to delete all but top 3 rows (N = 3), the expected result would be:



              |friends_name|
              ==============
              | Arunji |
              | Roshit |
              | Misbahu |


              The DELETE statement from the currently selected answer will result in:



              |friends_name|
              ==============
              | Arunji |
              | Misbahu |
              | Misbahu |
              | Misbahu |


              See this sqlfiddle. The reason for this is that it first sorts names alphabetically, then takes top 3, then deletes all that don't equal that. But since they are sorted by name they may not be the top 3 we want, and there's no guarantee that we'll end up with only 3.



              In the absence of unique indexes and other fields to determine what "top N" means, we go by the order returned by the database. We could be tempted to do something like this (substitute 99999 with however high number):



              DELETE FROM names LIMIT 99999 OFFSET 3


              But according to MySQL docs, while the DELETE supports the LIMIT clause, it does not support OFFSET. So, doing this in a single query, as requested, does not seem to be possible; we must perform the steps manually.



              Solution 1 - temporary table to hold top 3



              CREATE TEMPORARY TABLE temp_names LIKE names;
              INSERT INTO temp_names SELECT * FROM names LIMIT 3;
              DELETE FROM names;
              INSERT INTO names SELECT * FROM temp_names;


              Here's the sqlfiddle for reference.



              Solution 2 - new table with rename



              CREATE TABLE new_names LIKE names;
              INSERT INTO new_names SELECT * FROM names LIMIT 3;
              RENAME TABLE names TO old_names, new_names TO names;
              DROP TABLE old_names;


              Here's the sqlfiddle for this one.



              In either case, we end up with top 3 rows in our original table:



              |friends_name|
              ==============
              | Arunji |
              | Roshit |
              | Misbahu |





              share|improve this answer
























                up vote
                0
                down vote













                I just wanted to follow up on this relatively old question because the existing answers don't capture the requirement and/or are incorrect. The question states the names can be repeated, but only the top N must be preserved. Other answers will delete incorrect rows and/or incorrect number of them.



                For example, if we have this table:



                |friends_name|
                ==============
                | Arunji |
                | Roshit |
                | Misbahu |
                | Misbahu |
                | Roshit |
                | Misbahu |
                | Rohan |


                And we want to delete all but top 3 rows (N = 3), the expected result would be:



                |friends_name|
                ==============
                | Arunji |
                | Roshit |
                | Misbahu |


                The DELETE statement from the currently selected answer will result in:



                |friends_name|
                ==============
                | Arunji |
                | Misbahu |
                | Misbahu |
                | Misbahu |


                See this sqlfiddle. The reason for this is that it first sorts names alphabetically, then takes top 3, then deletes all that don't equal that. But since they are sorted by name they may not be the top 3 we want, and there's no guarantee that we'll end up with only 3.



                In the absence of unique indexes and other fields to determine what "top N" means, we go by the order returned by the database. We could be tempted to do something like this (substitute 99999 with however high number):



                DELETE FROM names LIMIT 99999 OFFSET 3


                But according to MySQL docs, while the DELETE supports the LIMIT clause, it does not support OFFSET. So, doing this in a single query, as requested, does not seem to be possible; we must perform the steps manually.



                Solution 1 - temporary table to hold top 3



                CREATE TEMPORARY TABLE temp_names LIKE names;
                INSERT INTO temp_names SELECT * FROM names LIMIT 3;
                DELETE FROM names;
                INSERT INTO names SELECT * FROM temp_names;


                Here's the sqlfiddle for reference.



                Solution 2 - new table with rename



                CREATE TABLE new_names LIKE names;
                INSERT INTO new_names SELECT * FROM names LIMIT 3;
                RENAME TABLE names TO old_names, new_names TO names;
                DROP TABLE old_names;


                Here's the sqlfiddle for this one.



                In either case, we end up with top 3 rows in our original table:



                |friends_name|
                ==============
                | Arunji |
                | Roshit |
                | Misbahu |





                share|improve this answer






















                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  I just wanted to follow up on this relatively old question because the existing answers don't capture the requirement and/or are incorrect. The question states the names can be repeated, but only the top N must be preserved. Other answers will delete incorrect rows and/or incorrect number of them.



                  For example, if we have this table:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Roshit |
                  | Misbahu |
                  | Misbahu |
                  | Roshit |
                  | Misbahu |
                  | Rohan |


                  And we want to delete all but top 3 rows (N = 3), the expected result would be:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Roshit |
                  | Misbahu |


                  The DELETE statement from the currently selected answer will result in:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Misbahu |
                  | Misbahu |
                  | Misbahu |


                  See this sqlfiddle. The reason for this is that it first sorts names alphabetically, then takes top 3, then deletes all that don't equal that. But since they are sorted by name they may not be the top 3 we want, and there's no guarantee that we'll end up with only 3.



                  In the absence of unique indexes and other fields to determine what "top N" means, we go by the order returned by the database. We could be tempted to do something like this (substitute 99999 with however high number):



                  DELETE FROM names LIMIT 99999 OFFSET 3


                  But according to MySQL docs, while the DELETE supports the LIMIT clause, it does not support OFFSET. So, doing this in a single query, as requested, does not seem to be possible; we must perform the steps manually.



                  Solution 1 - temporary table to hold top 3



                  CREATE TEMPORARY TABLE temp_names LIKE names;
                  INSERT INTO temp_names SELECT * FROM names LIMIT 3;
                  DELETE FROM names;
                  INSERT INTO names SELECT * FROM temp_names;


                  Here's the sqlfiddle for reference.



                  Solution 2 - new table with rename



                  CREATE TABLE new_names LIKE names;
                  INSERT INTO new_names SELECT * FROM names LIMIT 3;
                  RENAME TABLE names TO old_names, new_names TO names;
                  DROP TABLE old_names;


                  Here's the sqlfiddle for this one.



                  In either case, we end up with top 3 rows in our original table:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Roshit |
                  | Misbahu |





                  share|improve this answer












                  I just wanted to follow up on this relatively old question because the existing answers don't capture the requirement and/or are incorrect. The question states the names can be repeated, but only the top N must be preserved. Other answers will delete incorrect rows and/or incorrect number of them.



                  For example, if we have this table:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Roshit |
                  | Misbahu |
                  | Misbahu |
                  | Roshit |
                  | Misbahu |
                  | Rohan |


                  And we want to delete all but top 3 rows (N = 3), the expected result would be:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Roshit |
                  | Misbahu |


                  The DELETE statement from the currently selected answer will result in:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Misbahu |
                  | Misbahu |
                  | Misbahu |


                  See this sqlfiddle. The reason for this is that it first sorts names alphabetically, then takes top 3, then deletes all that don't equal that. But since they are sorted by name they may not be the top 3 we want, and there's no guarantee that we'll end up with only 3.



                  In the absence of unique indexes and other fields to determine what "top N" means, we go by the order returned by the database. We could be tempted to do something like this (substitute 99999 with however high number):



                  DELETE FROM names LIMIT 99999 OFFSET 3


                  But according to MySQL docs, while the DELETE supports the LIMIT clause, it does not support OFFSET. So, doing this in a single query, as requested, does not seem to be possible; we must perform the steps manually.



                  Solution 1 - temporary table to hold top 3



                  CREATE TEMPORARY TABLE temp_names LIKE names;
                  INSERT INTO temp_names SELECT * FROM names LIMIT 3;
                  DELETE FROM names;
                  INSERT INTO names SELECT * FROM temp_names;


                  Here's the sqlfiddle for reference.



                  Solution 2 - new table with rename



                  CREATE TABLE new_names LIKE names;
                  INSERT INTO new_names SELECT * FROM names LIMIT 3;
                  RENAME TABLE names TO old_names, new_names TO names;
                  DROP TABLE old_names;


                  Here's the sqlfiddle for this one.



                  In either case, we end up with top 3 rows in our original table:



                  |friends_name|
                  ==============
                  | Arunji |
                  | Roshit |
                  | Misbahu |






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 10 at 18:00









                  Unix One

                  946314




                  946314



























                       

                      draft saved


                      draft discarded















































                       


                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f17292375%2fdelete-all-rows-except-first-n-from-a-table-having-single-column%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Top Tejano songwriter Luis Silva dead of heart attack at 64

                      政党

                      天津地下鉄3号線