Keep Only N Last Records in SQLite Database, Sorted by Date

Keep only N last records in SQLite database, sorted by date

To delete all but the latest 10 records.

delete
from test
where id not in (
    select id
    from test
    order by date desc
    limit 10
)

Android SQLite Query - Getting latest 10 records

Change the DESC to ASC and you will get the records that you want, but if you need them ordered, then you will need to reverse the order that they come in. You can either do that in your own code or simply extend your query like so:

select * from (
    select *
    from tblmessage
    order by sortfield ASC
    limit 10
) order by sortfield DESC;

You really should always specify an order by clause, not just ASC or DESC.

Delete all but latest 500 rows from sqlite database

something along these lines, maybe?

delete from contents where juliandate <= (
    select max(juliandate) from (
        select juliandate from contents order by juliandate limit 0, 50));

You can use id instead of juliandate or any other field which value increases with every insert.

How to get Last record from Sqlite?

Try this:

SELECT * 
    FROM    TABLE
    WHERE   ID = (SELECT MAX(ID)  FROM TABLE);

OR

you can also used following solution:

SELECT * FROM tablename ORDER BY column DESC LIMIT 1;

Sqlite query to keep the last 5 searches on android

In your database, I'm not seeing any column with the name date; you have a field named time.

You need to change your code like:

db.execSQL("DELETE FROM placesearch " +
                "WHERE id < (" +
                " SELECT MIN(id)" +
                " FROM (SELECT id" +
                " FROM placesearch" +
                " ORDER BY time DESC" +
                " LIMIT ?))", args);

Query on Sqlite to group only the three most recent records for each user

First, get all user IDs:

SELECT DISTINCT UserID FROM MyTable;

Then for each such user ID, determine the smallest date that we want to handle, by sorting that user's records by the date, and picking the third one:

SELECT UserID,
       (SELECT Date
        FROM MyTable
        WHERE UserID = Users.UserID
        ORDER BY Date DESC
        LIMIT 1 OFFSET 2
       ) AS MinDate
FROM (SELECT DISTINCT UserID
      FROM MyTable
     ) AS Users;

UserID     MinDate
U6TH16001  2016-11-07
U6TH16002  2016-10-10
U6TH16003  NULL
U6TH15001  2016-11-07

Then we can join these values back with the original table to get only the desired records, and finally do the aggregation over them:

SELECT MyTable.UserID,
       SUM(Status),
       COUNT(*)
FROM MyTable
JOIN (SELECT UserID,
             (SELECT Date
              FROM MyTable
              WHERE UserID = Users.UserID
              ORDER BY Date DESC
              LIMIT 1 OFFSET 2
             ) AS MinDate
      FROM (SELECT DISTINCT UserID
            FROM MyTable
           ) AS Users
     ) AS MinDates
     ON MyTable.UserID = MinDates.UserID
    AND MyTable.Date  >= IFNULL(MinDates.MinDate, 0)
GROUP BY MyTable.UserID;

how to get last 10 added records in sqlite table in iphone

Every row in a table has an unique ID, which you can use for your query:

SELECT
    *
FROM
    ContentMaster
WHERE
    ContentAddedByUserID='%@'
AND
    HiveletCode='%@'
ORDER BY
    rowid DESC
LIMIT 10

SQLite: How to SELECT most recent record for each user from single table with composite key?

You could try this:

select user_id, max(last_updated) as latest
from records
group by user_id

This should give you the latest record per user. I assume you have an index on user_id and last_updated combined.

In the above query, generally speaking - we are asking the database to group user_id records. If there are more than 1 records for user_id 1, they will all be grouped together. From that recordset, maximum last_updated will be picked for output. Then the next group is sought and the same operation is applied there.

If you have a composite index, sqlite will likely just use the index because the index contains both fields addressed in the query. Indexes are smaller than the table itself, so scanning or seeking is faster.

Sqlite - get the most recent record per table

Use correlated subqueries to lookup the corresponding names:

SELECT id,
       (SELECT FirstName
        FROM TableTwo
        WHERE NameID = TableOne.id
        ORDER BY Date DESC
        LIMIT 1
       ) AS FirstName,
       ...
FROM TableOne;

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

---

Related Topics