如何删除SQL结果中最旧的条目? [英] How can i remove the oldest entries in my SQL result?

问题描述

我只想获取所有项目的最新报告，但是每个item_id仅获得最新的报告. 这是我当前的解决方案:

I want to get only the last reports, for all items but only the most recent report for each item_id. Here is my current solution:

SELECT distinct * FROM t.reports ORDER BY created DESC LIMIT 100

我的表由以下几列组成:

My table consists of the following columns:

id | user | item_id |  created
'2', '1',   '2643',  '2017-06-13 16:28:34'
'3', '1',   '19333', '2017-06-13 19:26:56'
'4', '1',   '19333', '2017-06-13 19:29:24'
'5', '1',   '1319',  '2017-06-13 19:29:56'
'6', '1',   '1319',  '2017-06-13 19:30:16'
'7', '1',   '1319',  '2017-06-13 19:30:17'
'8', '1',   '1319',  '2017-06-13 19:30:18'
'9', '1',   '1319',  '2017-06-13 19:30:25'
'10','1',   '1319',  '2017-06-13 19:31:51'

我不希望重复的item_ids，而只希望该项目的最新条目. 但是我也想要所有报告，但是没有重复的项目报告！

I want no duplicate item_ids AND only the most recent entry for that item. BUT i also want ALL reports, but no duplicate item reports!

示例: 我希望当我执行查询时，我只会得到返回的2,4和10行.

EXAMPLE: i expect that when i execute my query, i only get row 2,4 and 10 returned.

推荐答案

尝试一下:

select a.id,a.user,a.item_id,a.created
from reports as a
where a.created=(select max(created) 
                from reports as b
                where a.item_id=b.item_id)

这篇关于如何删除SQL结果中最旧的条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！