sql - 根据日期删除一对值中的一个

Question

假设我有以下值:

Reference      Class  Timestamp
XXHAG70        11     2013-05-07 14:29:59.820
XXHAG70        11     2013-05-07 14:33:19.780
XXHAG70        17     2013-05-07 14:30:19.930
XXHAG70        17     2013-05-07 14:33:44.690
PAF7010        06     2008-11-06 10:25:07.140
PAF7010        06     2009-02-27 12:56:11.420

每个类都有一个重复值，因此是成对的。我只想为每个引用中的每个类选择最旧的时间戳。

Answer 1

您可以使用 row_number 函数。

SELECT  Reference ,
        Class ,
        Timestamp
FROM    ( SELECT    Reference ,
                    Class ,
                    Timestamp ,
                    ROW_NUMBER() OVER ( PARTITION BY Reference, Class ORDER BY Timestamp) AS rnum
          FROM      MyTable
        ) A
WHERE   rnum = 1;