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

Posted on 2013-09-13
Medium Priority
Last Modified: 2013-09-16
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.

Question by:purplesoup
LVL 66

Expert Comment

by:Jim Horn
ID: 39490868
You need a subquery to pull this off.  Something like..

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

Open in new window

LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 668 total points
ID: 39490871
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
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 664 total points
ID: 39491052
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
LVL 49

Accepted Solution

PortletPaul earned 668 total points
ID: 39492515
>> 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:

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question