Solved

Picking latest entry to join to

Posted on 2014-10-29
4
122 Views
Last Modified: 2014-10-30
Hi All,

Quite a simple one hopefully.

I am joining to a table that has a number of entries for each ID. All info is the same for each ID, except [date] and [val]. In my join condition, I want to return all data relating to the most recent entry.

What's the best way of doing this?

Thanks
0
Comment
Question by:James Elliott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40410576
<total air code for a two-line question.  let me know if this works.>
SELECT st.id, st.goo, st.foo, st.boo, mt.max_date
FROM some_table st
   JOIN (SELECT id, max(date) as max_date from max_table GROUP BY id) mt ON st.id = mt.id

Open in new window


Also, I have an article out there called SQL Server GROUP BY Solutions that has lots of examples of how to join with/to aggregated data.
0
 
LVL 8

Assisted Solution

by:johny_bravo1
johny_bravo1 earned 250 total points
ID: 40410609
Not sure what exactly you are trying to achieve, but
you can use CROSS APPLY for joining latest record. If you can share your db structure with sample data, it might be useful. Although you can check CROSS APPLY
0
 
LVL 12

Author Closing Comment

by:James Elliott
ID: 40413056
Thanks both.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40413192
Thanks for the split.  For future reference though, more details provided in the original question, to include a data mockup of before / after = more precise answers we can give.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

738 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