Sqlite: How to Select "Most Recent Record for Each User" from Single Table with Composite Key

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.

How to get the latest date of entry for each user in Sqlite?

You can use max(date) to get the most recent date for each user.

SELECT
  user_id,
  MAX(date)
FROM answers
GROUP BY user_id,
ORDER BY date DESC;

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;

SQLAlchemy: select most recent row for all ids in a single table with composite primary key

Have you tried:

all_versions = db_session.query(User, func.max(User.timestamp)).\
               filter(User.timestamp <= timestamp).\
               group_by(User.id_)               

You can read more about generic functions in SQLAlchemy here

Select the latest 3 records for each ID in a table

You could look up the three most recent dates for each ID:

SELECT ID, Date, Value
FROM MyTable
WHERE Date IN (SELECT Date
               FROM MyTable AS T2
               WHERE T2.ID = MyTable.ID
               ORDER BY Date DESC
               LIMIT 3)

Alternatively, look up the third most recent date for each ID, and use it as a limit:

SELECT ID, Date, Value
FROM MyTable
WHERE Date >= IFNULL((SELECT Date
                      FROM MyTable AS T2
                      WHERE T2.ID = MyTable.ID
                      ORDER BY Date DESC
                      LIMIT 1 OFFSET 2),
                     0)

Both queries should get good performance from the primary key's index.

Query for existing rows with composite primary key in android sqlite

Your query works fine in the latest SQLite versions, but since you want to use it with Android, which supports only older versions (at the moment SQLite 3.32.2 in API Level 33), change to a subquery with the VALUES clause:

SELECT *
FROM table_name
WHERE (key1, key2, key3) IN (SELECT * FROM (VALUES (1,2,'foo'), (3,4,'bar')));

For older versions of SQLite use a subquery with UNION ALL (tested in version 3.15.1):

SELECT *
FROM table_name
WHERE (key1, key2, key3) IN (
  SELECT 1,2,'foo'
  UNION ALL
  SELECT 3,4,'bar'
);

or, with a join that works in any version:

SELECT t.*
FROM table_name t
INNER JOIN (
  SELECT 1 key1, 2 key2, 'foo' key3
  UNION ALL
  SELECT 3,4,'bar'
) u ON u.key1 = t.key1 AND u.key2 = t.key2 AND u.key3 = t.key3;

See the demo.

SQLite Join - query most recent posts by each user

You can use an inner query that gets you the latest post for each user, then that table will act as a filter when joined with the other two.

select  *
from    users u
join    (
            select  user_id, max(post_id) post_id
            from    Posts
            group by user_id
        ) r
on      u.user_id = r.user_id
join    posts p
on      r.post_id = p.post_id and
        r.user_id = p.user_id

---

Related Topics