CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Djbril
Djbril

Reputation: 895

Select old records in a table

I want the oldest row by date for each Distinct Number. I created this script but the problem is I keep on getting the newest record.

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]))

The Table:

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

Desired Result:

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

Any help would be appreciated. Thank You

Upvotes: 3

Views: 715

Answers (3)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

This way simple 

    SELECT  PSEUDO,  NUMBER , MIN ([LAST CONTACT DATE]) FROM [DATA].[DBO].[IAPT] T1
     GROUP BY PSEUDO,  NUMBER

Upvotes: 0

Usman Asghar
Usman Asghar

Reputation: 61

You should use MIN function instead of MAX function

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]))

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use ROW_NUMBER with a PARTITION BY clause:

SELECT Pseudo, Number, [Last Contact Date]
FROM (
  SELECT Pseudo, Number, [Last Contact Date],
         ROW_NUMBER() OVER (PARTITION BY Number 
                            ORDER BY [Last Contact Date]) AS rn
  FROM [Data].[dbo].[IAPT]) AS t
WHERE t.rn = 1

The first record within each Number partition is the one having the oldest date.

Upvotes: 1

Related Questions