On a Rails (5.2) app with PostgreSQL I have 2 tables: Item and ItemPrice where an item has many item_prices.
Table Item
| id | name |
|---|---|
| 1 | poetry book |
| 2 | programming book |
Table ItemPrice
| id | item_id | price |
|---|---|---|
| 1 | 1 | 4 |
| 2 | 2 | 20 |
| 3 | 1 | 8 |
| 4 | 1 | 6 |
| 5 | 2 | 22 |
I am trying to select all the items for which the last price (price of the last offer price attached to it) is smaller than the one before it
So in this example, my request should only return item 1 because 6 < 8, and not item 2 because 22 > 20
I tried various combinations of Active records and SQL subqueries that would allow me to compare the last price with the second to last price but failed so far.
ex Item.all.joins(:item_prices).where('EXISTS(SELECT price FROM item_prices ORDER BY ID DESC LIMIT 1 as last_price WHERE (SELECT price FROM item_prices ... can't work it out..
CodePudding user response：
You can do it as follows using ROW_NUMBER and LAG:
LAG to get the previous row based on a condition
WITH ranked_items AS (
SELECT m.*,
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id DESC) AS rn,
LAG(price,1) OVER (PARTITION BY item_id ORDER BY id ) previous_price
FROM ItemPrice AS m
)
SELECT it.*
FROM ranked_items itp
inner join Item it on it.id = itp.item_id
WHERE rn = 1 and price < previous_price