Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

Picking latest entry to join to

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
James Elliott
Asked:
James Elliott
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
Johny BravoCommented:
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
 
James ElliottManaging DirectorAuthor Commented:
Thanks both.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now