基于ID对行进行分组，然后返回每个ID分组的最低日期行[duplicate]

This question already has an answer here:

这个问题在这里已有答案:

• How can I remove duplicate rows? 36 answers

如何删除重复的行? 36个答案

suppose you have a data set having 3 columns: ID, user, date.

假设您有一个包含3列的数据集:ID,用户,日期。

is it possible to filter the data based on the minimum date even if some of the rows have identical IDs?

即使某些行具有相同的ID,是否可以根据最小日期过滤数据?

sorry if the question is a bit unclear. hopefully the image below will help clear things.

对不起,如果问题有点不清楚。希望下面的图片有助于清除事物。

there are two records having ID=1, with different users as well as dates. what i want to retrieve is the record having an ID=1, USER=A, DATE=2013-01-20 because its date is earlier than that of the second record (ID=1, USER=A, DATE=2013-01-21)

有两个记录ID = 1,具有不同的用户和日期。我要检索的是具有ID = 1,USER = A,DATE = 2013-01-20的记录,因为其日期早于第二条记录的日期(ID = 1,USER = A,DATE = 2013-01 -21)

i want to achieve the same effect for the three records having an ID=2. the desired record is ID=2,USER=C,DATE=2013-10-20

我想为ID = 2的三个记录实现相同的效果。所需记录为ID = 2,USER = C,DATE = 2013-10-20

basically i want to group these records by their IDs and then from that grouping, get the one with the lowest date

基本上我想按照他们的ID对这些记录进行分组,然后从该分组中获取具有最低日期的记录

3 个解决方案

#1

SELECT id, user, date
FROM OriginalData od
WHERE date = (SELECT MIN(date) 
              FROM OriginalDate od1 
              WHERE od.id = od1.id)

#2

Select * from table_name where date in 
( select MIN(date) from table_name)

#3

You have tyo use Group By clause on Id attribute.

您已在Id属性上使用Group By子句。

Use the following syntax

使用以下语法

select * from tab1 where (id, date) in (select id, min(date) from tab1 group by(id))