selects the last item by date

Question

I have 2 types of items, 1000118500 and 1000438300 and I would like to know how to bring only the last item by the newest date.

so I would have only 2 lines, the newest ones

id item       created
1  1000438300 2018-10-26 00:43:30
2  1000438300 2018-10-16 00:44:02
3  1000118500 2018-10-13 00:41:27
4  1000438300 2018-09-26 00:47:28
5  1000118500 2018-08-09 00:40:09
6  1000438300 2018-07-12 12:05:37
7  1000118500 2018-06-28 20:06:24
8  1000118500 2018-06-28 20:06:07

could anyone help? I'm using MariaDB

I uploaded an image and didn't upload it, I posted some lines — Peter Parker, Sep 10 '20 at 09:12
And listen @peter, with great power comes great responsibility :D — Sнаđошƒаӽ, Sep 10 '20 at 09:17

score 0 · Answer 1 · answered Sep 10 '20 at 09:15

0

You can filter with a subquery to get the latest row per item:

select t.*
from mytable t
where created = (select max(t1.created) from mytable t1 where t1.item = t.item)

This query would take advantage of an index on (item, created_at).

answered Sep 10 '20 at 09:15

GMB

216,147
25
84
135

Yes, but you use an unnecessary subquery, didn't you ? – coytech Sep 10 '20 at 09:22
@coytech: the subquery retrieves that latest date per item, so it is necessary. – GMB Sep 10 '20 at 09:33

coytech · Accepted Answer · 2020-09-10T09:27:16.507

0

Did you try the MAX function

SELECT item, MAX(created)
    FROM table
GROUP BY item

or see this post : How to select id with max date group by category in PostgreSQL?

edited Sep 10 '20 at 09:27

answered Sep 10 '20 at 09:15

coytech

193
7

score -1 · Answer 3 · answered Sep 10 '20 at 09:14

-1

SELECT * FROM [TABLE_NAME] ORDER BY created DESC LIMIT 1

answered Sep 10 '20 at 09:14

AndreiXwe

723
4
19

I think he wants 1 results by item not for all the table – coytech Sep 10 '20 at 09:18
Incorrect. OP wants the last from each item. Mentioned specifically that 2 rows need to be returned – Sнаđошƒаӽ Sep 10 '20 at 09:19

score -1 · Answer 4 · answered Sep 10 '20 at 09:14

Assuming that created is a DateTime column, select what you want and order it by the date in descending order, then limit the result to 1 row, that will be the row with the newest date

select item 
from tablename
where id = 1000438300
order by created desc
limit 1

selects the last item by date

4 Answers4