Getting the most recent row from a joined table based on date

I've two tables. They link together with a many-to-one join, but in fact I want a one-to-one join where the most recent (MAX(datecolumn)) is the row I want to join on from the "many" table.

What is the best way to achieve this? I know one way is a group by and MAX(..) but I feel that may not be the best option.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You need a subquery to pull this off.  Something like..

SELECT,,,, m.date_max
FROM TableOne o
   JOIN (SELECT one_id, Max(datecolumn) as date_max
             FROM TableMany 
             GROUP BY id) m ON = m.one_id AND o.SomeDateColumn = m.date_max

Open in new window

Aneesh RetnakaranDatabase AdministratorCommented:
You can make use of the row_number ()

;With c as (
select col1, col2, rn = ROW_NUMBER() OVER(PARTITION BY yourPrimaryKeys ORDER BY DateColumn )
FROM Tabl1
inner join Table1  on co1 = c1
SELECT * from c where rn = 1
Brian CroweDatabase AdministratorCommented:
This would be more specific if you provided some schema details but...

WITH cteMany AS (manyTableID, oneTableID, columnA, columnB, RowNumber)
   SELECT columnA, columnB,
   FROM manyTable
SELECT <columns from oneTable>, cteMany.columnA, cteMany.columnB
FROM oneTable
   ON oneTable.oneTableID = cteMany.oneTableID
   AND cteMany.RowNumber = 1
>> I want a one-to-one join
>> where the most recent (MAX(datecolumn)) is the row I want to join on
what a good description! - thank you

You have already been presented with options suggesting row_number(), and I would suggest this also, but let's compare that option to your statements.

A. I want a one-to-one join :: so you must select a subset from the 'many table'
B. where the most recent .. datecolumn ... is the row I want to join on
row_number() returns integers starting at 1 and it allows an "order by"
So if we order by "datecolumn" descending we get 1 for the "most recent"
- not only this,
row_number() also allows for "partition by" so that we start at 1 for each partition
In combination
if we partition, and order "datecolumn" descending, we get a value of 1 for the "most recent" record of each partition.

so, we now have a way to achieve point A. above.
All we do is select where row_number() has provided 1

>>I know one way is a group by and MAX(..) but I feel that may not be the best option.
MAX(..) is a way to get a value for one field, but a query using group by does not directly return rows from a table - instead it produces a resultset of calculations - which is quite a different thing.

Hopefully this helps. I would use row_number() for this need.
Further reading:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.