returning most current record

Question

Post reply

returning most current record

Gary Lewis-387807

SSC Journeyman

Points: 88
More actions
December 28, 2006 at 2:58 pm

#118489

HELP!!!
I'm sure this will be an easy question for most. I can't seem to figure out how to do this seeminly easy query.
I have 2 tables. One is the main table to the app. and the other is an audit table to capture certain changes to the main table. The audit table has one or more records for a given id in the main table. My problem is that I need to get, from the audit table, the most recent record(s) so I can undo a mass change query that one of our developers ran. The audit table has the date of the change which is great but I don't know how to structure the query to just return one record per id with that one record being the newest dated record. I could do it with some TSQL programming and a cursor but I would like to avoid that if possible. Any help will be appreciated. I have been spending too much time on this and need to get it done.
Thanks,
Gary

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

lost my post I guess.. here goes again:

you need to know the key columns for the tables:

SELECT key columns, MAX( change_date--orwhatevertheauditcolumnis)

FROM audittable GROUP BY key columns

then you can use this to join and update

John Chapman SSC-Addicted Points: 457 More actions · Answer 2

Do you have a field that captures the time of each record ?

For the purposes of this discussion, lets assume that you have a "Date_Time" field for each record (and it's of type datetime).

Your query is then:

Select top 1 * from audittable order by Date_Time desc

Note: If temporal critera is important for your queries consider building an index or specifying a primary key based on associated column/s.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Can you give us the structure of the audit table and some sample data?

Have multiple rows been changed? How are those records identified in the audit table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bharat Godha-379528 Newbie Points: 3 More actions · Answer 4

I believe that is a link between the audit table and the master table and identity field in audit table. If there is, below query should return you all the latest records from the audit table for each record in mater table.

SELECt * FROM master_table mt JOIN dbo.Exam e

JOIN audit_table at1

mt.key_column = at1.key_column_from_master_table

WHERE at1.unique_field_identity = (SELECT max(at2.unique_field_identity)

FROM audit_table at2

WHERE at1.key_column_from_master_table = at2.key_column_from_master_table)

If there is no identity field the you can use the date field...

SELECt * FROM master_table mt JOIN dbo.Exam e

JOIN audit_table at1

mt.key_column = at1.key_column_from_master_table

WHERE at1.audit_date = (SELECT max(at2.audit_date)

FROM audit_table at2

WHERE at1.key_column_from_master_table = at2.key_column_from_master_table)

Hope this works for you...

Gary Lewis-387807 SSC Journeyman Points: 88 More actions · Answer 5

Thanks to all for helping me out with this simple query. I don't know why I couldn't get my mind around this. I took the query from Bharat and modified for my situation and it worked without any problems.

Thanks again and Happy New Year to all!!!