• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

Query to get the latest modified records

My table A contains records with multiple entries of same ID (Attached Table A structure) also it contains modified date column as well.

Now i require a query to get only the latest modified records in my result set.(Attached the expected result as well). Please help
TableExpectedresult.xls
0
tks_g100
Asked:
tks_g100
2 Solutions
 
jamesguCommented:
select * from Table_A a
where not exists ( select 1 from Table_A b where a.asset_id = b.asset_id and b.modified_date > a.modified_date)

0
 
Kevin CrossChief Technology OfficerCommented:
SELECT *
FROM Table_A a
INNER JOIN (SELECT asset_id, max(modified_date) AS max_modified_date FROM Table_A GROUP BY asset_id) b
ON a.asset_id = b.asset_id AND a.modified_date = b.max_modified_date
0
 
tks_g100Author Commented:
Thanks for the query. Both yielded the results
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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