Sort based on TimeStamp

[Abhinav]

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

[KeithM]

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

Code: Select all

Select * 
From Your_Table 
Where Update_Date in 
   (Select Max(Update_Date) from Your_Table)

[Abhinav]

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

Code: Select all

Select * 
From Your_Table 
Where Update_Date in 
   (Select Max(Update_Date) from Your_Table)

I am checking for duplicates and inserting records into my target table with a void record, current record and the new record.

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

[KeithM]

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.

[Abhinav]

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.

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.

Is there a way i can do it with my timestamp column?

Thanks

Abhi

[chulett]

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...

[Abhinav]

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...

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.

Thanks

Abhi

[chulett]

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.

[Abhinav]

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.

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.

As hash file could handle only upto 2gb right.

Thanks

Abhi

[Abhinav]

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.

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.

As hash file could handle only upto 2gb right.

Thanks

Abhi

[chulett]

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:

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'm not sure how you'd do something like this without a hash file.

[Abhinav]

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:

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'm not sure how you'd do something like this without a hash file.

i will try something with this, will get back to u if any issues.

thanks a lot

Abhi