retrieving rows with max date

Question

I have two tables like this:

Table1 (Number column is unique)

Number | date

1234      2008-10-06 17:11:00

5678      2005-10-19 16:20:00

9023      2005-12-09 16:20:00

4243      2009-01-06 17:11:00

5234      2009-01-14 17:11:00



Table 2 

Number | code  

1234     A1   

1234     B1 

5678     A1

9023     A1

4243     C1

5234     C1

I am trying to retrieve data from these two tables so that I get only one row for each code in Table 2 that is the most recent (from table 1).

Based on this example, my result would be:

1234 A1 (because thats the one with latest date)

1234 B1 (because thats the one with latest date)

5234 C1 (because thats the one with latest date)

Answer 1

Select Distinct T1.number, T2.code 
From Table2 T2 
   Join Table1 T1
       On T1.Number = T2.Number
           And T1.date =
              (Select Max(Date) From Table1
               Where Number = T2.Number)

EDIT: to fix issue noted in comment:

 Select Z.Number, Z.Code
  From (Select A.number, A.code, B.date
        From Table2 A Join Table1 B
           On B.Number = A.Number) Z
  Where Z.Date =
      (Select Max(Date) 
       From Table2 A Join Table1 B
           On B.Number = A.Number
       Where code = Z.Code)

Answer 2

Analytic function solution. This is for Oracle; if you're using another RDBMS it may not work. If there are multiple rows with the same date for a given code, this will arbitrarily select one.

SELECT number, code FROM (
  SELECT t1.number,
         t1.code,
         row_number() OVER ( PARTITION BY t1.code ORDER BY t2.date DESC ) date_sort_key
    FROM t1, t2
    WHERE t2.number = t1.number
  )
  WHERE date_sort_key = 1

Substituting rank() for row_number() would make it report multiple entries where there is a duplicate date.

Answer 3

This works for SQL SERVER

CREATE table Table1 (number int, date datetime)
INSERT Table1 VALUES (1234, '2008-10-06 17:11:00')
,(5678, '2005-10-19 16:20:00')
,(9023, '2005-12-09 16:20:00')
,(4243, '2009-01-06 17:11:00')
,(5234, '2009-01-14 17:11:00')

CREATE table Table2 (number int, code varchar(2))
INSERT Table2 VALUES (1234, 'A1   ')
,(1234, 'B1') 
,(5678, 'A1')
,(9023, 'A1')
,(4243, 'C1')
,(5234, 'C1')

SELECT DISTINCT
  a.number
 ,a.code
FROM Table2 a
  INNER JOIN Table1 b ON a.number = b.number
  INNER JOIN (
    SELECT
      t2.code
     ,MAX(t1.date) as date
    FROM Table2 t2
      INNER JOIN Table1 t1
        ON t1.Number = t2.Number
    GROUP BY t2.code
  ) c ON b.date = c.date

Answer 4

Here is a version of Dave's answer that works on SQL Server

 SELECT number, code FROM (
  SELECT Table2.number,
         Table2.code,
         row_number() OVER ( PARTITION BY table2.code ORDER BY table1.date DESC ) date_sort_key
    FROM table1, Table2
    WHERE Table2.number = table1.number
  ) a
  WHERE date_sort_key = 1