如何获取每个外键的带有最小日期的记录的记录ID？

Question

我有下表

recordID               createdDate                         ForeignKeyID
00QA000000PtFXaMAN     2012-01-03 13:23:36.000             001A000000ngM21IAE
00QA000000OS2QiMAL     2011-12-15 12:03:02.000             001A000000ngM21IAE
.
.
.
.

我正在尝试获取foreignKeyID的recordID，其中createdDAte是foreignKeyID的分钟（createdDate）

如果recordID是identity int我可以通过执行以下查询

Select min(recordId),ForeignkeyID
from table 
group by ForeignKeyId

我本来以为我可以使用以下查询创建临时表，然后将其连接到minDate和foreignKeyID上的表，但是随后我发现，foreignKeyId的多个记录具有完全相同的createdDate。

Select min(createdDate) as minDate,ForeignKeyID
from table
group by ForeignKeyId

我对使用临时表或子查询或其他任何东西都持开放态度。谢谢。

Answer 1

做到这一点的方法之一是

select A.ForeignKeyID, R.recordID
from (select distinct t.ForeignKeyID from table as t) as A
    outer apply
    (
        select top 1 t.recordID
        from table as t where t.ForeignKeyID = A.ForeignKeyID
        order by t.createdDate asc
    ) as R

SQL范例

另一种方法是

select top 1 with ties
    t.recordID, t.ForeignKeyID
from table as t
order by row_number() over (partition by t.ForeignKeyID order by t.createdDate)

SQL范例

还有另一种方式

select A.recordID, A.ForeignKeyID
from
(
    select
        t.recordID, t.ForeignKeyID,
        row_number() over (partition by t.ForeignKeyID order by t.createdDate) as RowNum
    from table1 as t
) as A
where A.RowNum = 1

SQL范例

由于代码短，我比其他人更喜欢第二个