sql-server - 选择表中的旧记录

Question

我想要每个不同编号的日期最旧的行。我创建了这个脚本，但问题是我一直在获取最新记录。

SELECT*
FROM
     [Data].[dbo].[IAPT] t1
WHERE
      [Last Contact Date] IN 
                              (SELECT MAX([Last Contact Date]) 
                               FROM [Data].[dbo].[IAPT] 
                                WHERE t1.[Number] =  [Data].[dbo].[IAPT].[Number] 
                          AND 
      [Last Contact Date] NOT IN
                              (SELECT MAX([Last Contact Date]) 
                               FROM [Data].[dbo].[IAPT] 
                               WHERE t1.[Pseudo] =  [Data].[dbo].[IAPT].[Pseudo]))

表格:

Pseudo  Number    Last Contact Date
0X1     18        17/06/2013
0X1     18        16/04/2013
0X2     19        25/04/2013
0X2     19        16/07/2013

期望的结果:

Number      Last Contact Date
1           16/04/2013
2           25/04/2013

如有任何帮助，我们将不胜感激。谢谢你

Answer 1

您应该使用 MIN 函数而不是 MAX 函数

SELECT*
FROM
     [Data].[dbo].[IAPT] t1
WHERE
      [Last Contact Date] IN 
                              (SELECT MIN([Last Contact Date]) 
                               FROM [Data].[dbo].[IAPT] 
                                WHERE t1.[Number] =  [Data].[dbo].[IAPT].[Number] 
                          AND 
      [Last Contact Date] NOT IN
                              (SELECT MIN([Last Contact Date]) 
                               FROM [Data].[dbo].[IAPT] 
                               WHERE t1.[Pseudo] =  [Data].[dbo].[IAPT].[Pseudo]))