SQL Select record with the most recent date

Question

I want to select the record with the most recent date from an INNER JOINED table.

Select table1.field1, table2.field1 
FROM table1
INNER JOIN table2
ON table1.field1 = table2.field1
WHERE table1.field2 = table2.field2 AND most recent date

How to do that in SQL?

I think this is a duplicate of *every* one of the "Related Questions" shown on the right… — BRPocock, Feb 01 '14 at 11:00
possible duplicate of [SQL selecting rows by most recent date](http://stackoverflow.com/questions/189213/sql-selecting-rows-by-most-recent-date) — marc_s, Feb 01 '14 at 11:18

score 2 · Answer 1 · answered Feb 01 '14 at 10:57

2

Select table1.field1, t2.field1 
FROM table1
INNER JOIN 
(
   select field1, max(field2) as mdate
   from table2
   group by field1
) t2
ON table1.field1 = t2.field1 and table1.field2 = t2.mdate

answered Feb 01 '14 at 10:57

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Feb 01 '14 at 11:00

You need the TOP 1 record ordered by date (in descending order)

Select TOP 1 table1.field1, table2.field1 
FROM table1
INNER JOIN table2
ON table1.field1 = table2.field1
WHERE table1.field2 = table2.field2
ORDER BY date DESC

I might not be what you need if table2.field1 is not a unique field.

score 0 · Answer 3 · answered Feb 01 '14 at 11:08

Assuming that your table1 contains a field called date_created

Try this

Select table1.field1, table2.field1 
FROM table1
INNER JOIN table2
ON table1.field1 = table2.field1
WHERE table1.field2 = table2.field2 AND table1.date_created between '2012-03-18' and '2013-03-18'

SQL Select record with the most recent date

3 Answers3