I have a table like this
| datetime | name | value |
|---|---|---|
| 2021-09-22 10:00:01 | Apple | 100 |
| 2021-09-22 12:02:01 | Apple | 101 |
| 2021-09-22 12:00:02 | Lemon | 150 |
| 2021-09-23 18:10:01 | Orange | 10 |
| 2021-09-23 19:31:02 | Orange | 9 |
| 2021-09-24 09:00:00 | Apple | 99 |
| 2021-09-26 00:00:00 | Banan | 15 |
I would like to get the values for each name by max datetime field for this name.
| name | value | datetime |
|---|---|---|
| Apple | 99 | 2021-09-24 09:00:00 |
| Banan | 15 | 2021-09-26 00:00:00 |
| Orange | 9 | 2021-09-23 19:31:02 |
| Lemon | 150 | 2021-09-22 12:00:02 |
CodePudding user response：
Use distinct on:
select distinct on (name) t.*
from t
order by name, datetime desc;
distinct on is a convenient Postgres extension that returns the first row for a group -- defined by the keys in parentheses. "First" is based on the order by clause.
CodePudding user response：
This could help.
select distinct(name), value, datetime from table_name order by name asc, datetime desc;