Skip to content
Advertisement

How to fetch unique most recent items using IN from table in PostgreSQL

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement