SQLite query with LIMIT per column

Question

I am trying to compose a query with a where condition to get multiple unique sorted columns without having to do it in multiple queries. That is confusing so here is an example...

Price Table

id | item_id | date | price

I want to query to find the most recent price of multiple items given a date. I was previously iterating through items in my application code and getting the most recent price like this...

SELECT * FROM prices WHERE item_id = ? AND date(date) < date(?) ORDER BY date(date) DESC LIMIT 1

Iterating through each item and doing a query is too slow so I am wondering if there is a way I can accomplish this same query for multiple items in one go. I have tried UNION but I cannot get it to work with the ORDER BY and LIMIT commands like this thread says (https://stackoverflow.com/a/1415380/4400804) for MySQL

Any ideas on how I can accomplish this?

score 0 · Answer 1 · answered Feb 02 '20 at 04:56

0

Try this (based on adapting the answer):

SELECT * FROM prices a WHERE a.RowId IN (
  SELECT b.RowId
    FROM prices b
    WHERE a.item_id = b.item_id AND date < ?
    ORDER BY b.item_id LIMIT 1
) ORDER BY date DESC;

answered Feb 02 '20 at 04:56

jignatius

6,304
2
15
30

score 0 · Answer 2 · answered Feb 02 '20 at 08:12

Window functions (Available with sqlite 3.25 and newer) will likely help:

WITH ranked AS
 (SELECT id, item_id, date, price
       , row_number() OVER (PARTITION BY item_id ORDER BY date DESC) AS rn
  FROM prices
  WHERE date < ?)
SELECT id, item_id, date, price
FROM ranked
WHERE rn = 1
ORDER BY item_id;

will return the most recent of each item_id from all records older than a given date.

score 0 · Answer 3 · answered Feb 02 '20 at 14:53

I would simply use a correlated subquery in the `where` clause:

SELECT p.*
FROM prices p
WHERE p.DATE = (SELECT MAX(p2.date)
                FROM prices p2
                WHERE p2.item_id = p.item_id
               );

This is phrase so it works on all items. You can, of course, add filtering conditions (in the outer query) for a given set of items.

score 0 · Answer 4 · answered Feb 02 '20 at 15:01

With NOT EXISTS:

SELECT p.* FROM prices p
WHERE NOT EXISTS (
  SELECT 1 FROM prices
  WHERE item_id = p.item_id AND date > p.date
)

or with a join of the table to a query that returns the last date for each item_id:

SELECT p.*
FROM prices p INNER JOIN (
  SELECT item_id, MAX(date) date
  FROM prices 
  GROUP BY item_id
) t ON t.item_id = p.item_id AND t.date = p.date

SQLite query with LIMIT per column

4 Answers4