SQL: Inner Join return one row based on criteria

Question

This is probably simple, but i'm looking for the raw SQL to perform an INNER JOIN but only return one of the matches on the second table based on criteria.

Given two tables:

**TableOne**   
 ID  Name  
 1   abc  
 2   def

**TableTwo**  
 ID    Date     
 1     12/1/2014
 1     12/2/2014
 2     12/3/2014
 2     12/4/2014
 2     12/5/2014

I want to join but only return the latest date from the second table:

Expected Result:
1   abc   12/2/2014
2   def   12/5/2014

I can easily accomplish this in LINQ like so:

TableOne.Select(x=> new { x.ID, x.Name, Date = x.TableTwo.Max(y=>y.Date) });

So in other words, what does the above LINQ statement translate into in raw SQL?

Answer 1

There are two ways to do this:

1. Using GROUP BY and MAX():

   SELECT one.ID,
          one.Name,
          MAX(two.Date)
       FROM TableOne one
           INNER JOIN TableTwo two on one.ID = two.ID
       GROUP BY one.ID, one.Name
   

2. Using ROW_NUMBER() with a CTE:

   ; WITH cte AS (
       SELECT one.ID,
          one.Name,
          two.Date,
          ROW_NUMBER() OVER (PARTITION BY one.ID ORDER BY two.Date DESC) as rn
       FROM TableOne one
          INNER JOIN TableTwo two ON one.ID = two.ID
   )
       SELECT ID, Name, Date FROM cte WHERE rn = 1