How Do I Get The Record Id Of The Record With The Min Date For Each Foreign Key?

I have the following table recordID createdDate ForeignKeyID 00QA000000PtFXaMAN 2012-01-03 13:23:36.000 001A000000ngM21IAE 00Q

Solution 1:

One of the ways to do it is

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

SQL FIDDLE EXAMPLE

Another way to do it is

select top 1with ties
    t.recordID, t.ForeignKeyID
fromtableas t
orderbyrow_number() over (partitionby t.ForeignKeyID orderby t.createdDate)

SQL FIDDLE EXAMPLE

And another way

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

SQL FIDDLE EXAMPLE

I like second one more than others because of shortness of code

Solution 2:

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