我如何用最小日期为每个外键的记录的记录ID？(How do I get the record ID

我有以下表

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

我想获得的recordId为foreignKeyID其中createdDAte是foreignKeyID最小（createdDate）

如果是的recordId身份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实例FIDDLE

另一种方式来做到这一点是

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实例FIDDLE

而另一种方式

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实例FIDDLE

我喜欢比别人，因为代码急促的第二个更

Answer 2:

SELECT 
    recordID, createdDate, ForeignKeyID
FROM
  ( SELECT 
        recordID, createdDate, ForeignKeyID,
        ROW_NUMBER() OVER ( PARTITION BY ForeignKeyID 
                            ORDER BY createdDate, recordID
                          ) AS rn
    FROM 
        tableX
  ) AS t
WHERE 
    rn = 1 ;

文章来源: How do I get the record ID of the record with the min date for each foreign key?