Scenario
+--------+-------+------------+ | item | price | date | +--------+-------+------------+ | apple | 31 | 2019-09-11 | | banana | 11 | 2019-09-11 | | banana | 13.23 | 2019-10-12 | | apple | 30.1 | 2019-10-12 | | banana | 12.77 | 2019-10-31 | | apple | 32 | 2019-11-28 | | banana | 15.6 | 2019-11-02 | | banana | 12.8 | 2019-12-18 | | apple | 33.45 | 2020-01-12 | | banana | 16.78 | 2020-01-21 | | apple | 31.5 | 2020-01-27 | | banana | 14.4 | 2020-02-17 | | banana | 13.2 | 2020-03-12 | +--------+-------+------------+
I have a prices table from which I am trying to fetch price for both Banana and Apple among 100+ fruits using the following SQL query
SELECT * FROM prices WHERE item IN ('apple','banana') ORDER BY date DESC LIMIT 2;
Issue
This query returns me last two prices from the entire table, which are both for banana. However, I want the most recent price for Apple and Banana
Expected
+--------+-------+------------+ | apple | 31.5 | 2020-01-27 | | banana | 13.2 | 2020-03-12 | +--------+-------+------------+
Current
+--------+-------+------------+ | banana | 14.4 | 2020-02-17 | | banana | 13.2 | 2020-03-12 | +--------+-------+------------+
How can I achieve this?
Advertisement
Answer
Use distinct on
:
SELECT DISTINCT ON (p.item) p.* FROM prices p WHERE item IN ('apple', 'banana') ORDER BY p.item, p.date DESC