sql query help getting the correct record

erikTsomik
erikTsomik used Ask the Experts™
on
I have 2 table. 1 table keep all base files. The second table keep track of all uploaded files.

I need to pull only latest records for unique docKey.

In reality i need only record 2 and 3

The attached file has a screen shotscreen capture
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT IM.docKey, MU.filename, IM.name, MU.uploadID, MU.UploadedWhen 
FROM 
	InsManual IM WITH (NOLOCK) 
	OUTER APPLY (
		SELECT TOP 1 * 
		FROM InsManualUploads MUS WITH (NOLOCK) 
		WHERE MUS.docKey = IM.docKey 
		ORDER BY MUS.UploadedWhen DESC
	) MU 
ORDER BY IM.docKey

Open in new window

erikTsomikSystem Architect, CF programmer

Author

Commented:
can you explain outer apply how it works
This is the best resource that I am readily familiar with for that:
http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

It is a handy trick.  CROSS APPLY might also work for you.  Without knowing your data/project intimately, it would be hard to say.
awking00Information Technology Specialist

Commented:
select dockey, filename, name, uploadid, uploadedwhen from
(select im.dockey, mu.filename, im.name, mu.uploadid, mu.uploadedwhen,
 row_number() over (partition by mu.dockey order by mu.uploadedwhen desc) rn
 from
 insmanual im inner join insmanualuploads mu
 on im.dockey = mu.dockey) as x
where x.rn = 1
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Should be as simple as:
SELECT IM.docKey, MU.filename, IM.name, MAX(MU.UploadedWhen)
FROM InsManual IM 
INNER JOIN InsManualUploads MUS ON MUS.docKey = IM.docKey 
GROUP BY IM.docKey, MU.filename, IM.name
ORDER BY IM.docKey

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial