Sort based on TimeStamp
Moderators: chulett, rschirm, roy
Sort based on TimeStamp
Hi All,
I need to get my last updated record, its based on timestamp. Any help, suggestions to sort the data based on timestamp would be appreciated.
I am basically lookin to capture the most recent record.
Thanks
Abhi
I need to get my last updated record, its based on timestamp. Any help, suggestions to sort the data based on timestamp would be appreciated.
I am basically lookin to capture the most recent record.
Thanks
Abhi
Could you provide a few more details about what you are trying to do?
If you just want the last record update in a table it would be a simple Select statement
If you just want the last record update in a table it would be a simple Select statement
Code: Select all
Select *
From Your_Table
Where Update_Date in
(Select Max(Update_Date) from Your_Table)
Keith
I am checking for duplicates and inserting records into my target table with a void record, current record and the new record.KeithM wrote:Could you provide a few more details about what you are trying to do?
If you just want the last record update in a table it would be a simple Select statementCode: Select all
Select * From Your_Table Where Update_Date in (Select Max(Update_Date) from Your_Table)
That is if i have any duplicate in my current source file i would have 3 records in my target with the 1st one as is and copy forward the first one with a flag as -1 and the new record with the flag 1.
If i have 3 records (duplicates) then i would insert 5 records in my target as the first two would be inserted as is and 2 more records with a flag -1 in one of my columns and the latest one the last one with a 1.
That is at nay point of time in my target each record with its primary key from would jave a flag coulmn sum to 1 with all its old records having a copy record with -1 .
Its similar to CDC logic, the only diff is i need have my old record still present in my target.
The only way i could diff on my input rows would be a column with timestamp which has the date and time in it.
I need to capture the latest record from my source.
Any suggestions
Thanks
Abhi
Could you just add a current record flag? Then as you process a record you would check to see if it has a current one. If it does create your new -1 record, set the flag on the old one to false and create the new one with the flag set to true. If no record exists just create the new record with the flag set to true. That way the one that is current is always the one you want.
Keith
This doent work for me, i have many other coditions to deal with, what i need is how do i capture the latest duplicate record from source sequential based on a timestamp.KeithM wrote:Could you just add a current record flag? Then as you process a record you would check to see if it has a current one. If it does create your new -1 record, set the flag on the old one to false and create the new one with the flag set to true. If no record exists just create the new record with the flag set to true. That way the one that is current is always the one you want.
Is there a way i can do it with my timestamp column?
Thanks
Abhi
One approach is to build a reference hash with your current business keys and whatever other data you need to know about the current record. In your case, that would (at the very least) be the timestamp.
Order the inserts into the hash by business key, timestamp ascending to take advantage of the destructive overwrite that hash files do. The survivor will be your max(timestamp) per key.
Use the hash in your job to know if you need to insert or update.
Perhaps that would work for you...
Order the inserts into the hash by business key, timestamp ascending to take advantage of the destructive overwrite that hash files do. The survivor will be your max(timestamp) per key.
Use the hash in your job to know if you need to insert or update.
Perhaps that would work for you...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thank you Chulett, i guess this would work for me, anyways i will try this and will get back to you if it doesnt work.chulett wrote:One approach is to build a reference hash with your current business keys and whatever other data you need to know about the current record. In your case, that would (at the very least) be the timestamp.
Order the inserts into the hash by business key, timestamp ascending to take advantage of the destructive overwrite that hash files do. The survivor will be your max(timestamp) per key.
Use the hash in your job to know if you need to insert or update.
Perhaps that would work for you...
Thanks
Abhi
Can you send theSQL solution, i think that would be better off as the hash file would be not a good idea in the long run.chulett wrote:You could also use a variation of the SQL Keith posted so that the source database does all the work of figuring out the max(timestamp). I'd still put the result in a hash and leverage that in your job.
Either way.
As hash file could handle only upto 2gb right.
Thanks
Abhi
Can you send the SQL solution, i think that would be better off as the hash file would be not a good idea in the long run.chulett wrote:You could also use a variation of the SQL Keith posted so that the source database does all the work of figuring out the max(timestamp). I'd still put the result in a hash and leverage that in your job.
Either way.
As hash file could handle only upto 2gb right.
Thanks
Abhi
A 32bit hash file can't be larger than 2GB. However, you'll find lots of traffic here on the subject of creating 64bit hash files which don't have that limit.
By 'SQL solution' I meant having the source query that builds the hash file compute the max timestamp for you, rather than pumping all rows into it and having the max be the survivor. Something like Keith posted, but more like a correlated subquery:
I'm not sure how you'd do something like this without a hash file. ![Confused :?](./images/smilies/icon_confused.gif)
By 'SQL solution' I meant having the source query that builds the hash file compute the max timestamp for you, rather than pumping all rows into it and having the max be the survivor. Something like Keith posted, but more like a correlated subquery:
Code: Select all
Select *
From Your_Table a
Where Update_Date =
(Select Max(Update_Date) from Your_Table b
where b.key = a.key)
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
chulett wrote:A 32bit hash file can't be larger than 2GB. However, you'll find lots of traffic here on the subject of creating 64bit hash files which don't have that limit.
By 'SQL solution' I meant having the source query that builds the hash file compute the max timestamp for you, rather than pumping all rows into it and having the max be the survivor. Something like Keith posted, but more like a correlated subquery:
I'm not sure how you'd do something like this without a hash file.Code: Select all
Select * From Your_Table a Where Update_Date = (Select Max(Update_Date) from Your_Table b where b.key = a.key)
i will try something with this, will get back to u if any issues.
thanks a lot
Abhi