How to remove records if more than 20



  • The thing is, I need to delete if the records are more than 20 and only the old ones, that's the date. I found a SQL request somewhere.

    DELETE tt
    FROM 
      ( SELECT DISTINCT uid             --- these 3 lines can be replaced
        FROM tableX                     --- with:   UserTable AS du
      ) AS du                           --- a table that you probably have
    JOIN
      tableX AS tt
    ON  tt.uid = du.uid
    AND tt.timestampColumn >
        ( SELECT timestampColumn AS ts
          FROM tableX
          WHERE uid = du.uid 
          ORDER BY ts ASC
          LIMIT 1 OFFSET 199
        )
    

    But that's not clear. I can remove more than 20, but the problem is, two checks, and I'm in the SQLite newcomers, a pot in the head.

    I have these poles:

    private static final String TABLE_SEARCH = "search_table";
    private static final String KEY_ID = "id";
    private static final String KEY_DATE = "date";
    

    How do I use the request above? And how do you redo db.delete?



  • DELETE FROM search_table
    WHERE date NOT IN (
        SELECT date 
        FROM search_table 
        ORDER BY date desc 
        LIMIT 20
    )
    

    He was on his knee, there might be fingerprints, but that's the idea.
    Internally, we choose everything that needs to be left, i.e. a top of 20 records on the date, and externally, we remove everything, except that we chose the inside.

    Here's another option, this better one:

    DELETE FROM search_table
    

    WHERE id < SELECT id from (
    SELECT id, date
    FROM search_table
    ORDER BY date desc
    LIMIT 1 OFFSET 19
    )




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2