Solved

Picking latest entry to join to

Posted on 2014-10-29
4
119 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

816 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now