Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Lates Timestamp record from 3 tables.

 
Shaan Shar
Ranch Hand
Posts: 1249
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ranchers I have one question to ask Let me describe the situation:

There are 3 tables which I need to joint on a single field CALLED AS ID

Now in each table there is a field called as Updt_Timestamp.

Now the problem statement is

Select the record for that particular ID (Which is given ofcourse) from the 3 tables but finally there should be a single record with the latest Updt_Timestamp among those 3 records.



Now I know one method which is, first take all the records in three cursur and then compare there timestamps and then create a flag which will indicate us that from which cursor we are finally taking the record.......

Isn't there any appropriate method.

Because in this flow we are using 3 cursors which are not finally usable only one of them is going to be used.


Early replies are appreciated.

 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well Your question is not clear to me , could you rephrase it. I can interpret it in two way
  • You want to select from 3 table but timestap should be lates among 3
  • You want to select a data which has latest timestamp


  • both point have different meaning,and I am also not clear about your joins.
    if you are planning for kinda inner join then there would be on cursor etc

    Shailesh
     
    Shaan Shar
    Ranch Hand
    Posts: 1249
    Java Spring Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks Shailesh for the early reply,

    I want to select the record among these 3 tables which is having Latest Update_Timestamp...







    Let suppose there are three tables a,b,c in

    a ======== 20 Columns
    b ======== 30 Columns
    c ======== 40 Columns

    If a has the record with the latest timestamp then we should retrieve record of a which has 20 columns......

    I hope I cleraed all confusion but still if anyone have any concern then revert back.
     
    stu derby
    Ranch Hand
    Posts: 333
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You should be able to do this in 2 queries; one to get the timestamps from A, B, or C, and one to get the A, B or C record you need.

    You then compare the 3 different values in Java and then go execute a select against the right table for whatever columns you need.
     
    Shaan Shar
    Ranch Hand
    Posts: 1249
    Java Spring Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks Stu,

    But that I know already but as you can see that there is a multiple hit on Database which is not good for performance tuning.....

    The method, earlier I explained is the other way and also prevent multiple hitting the Database I need some more better Query.



     
    Shaan Shar
    Ranch Hand
    Posts: 1249
    Java Spring Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    SELECT a.Update_Timestamp as a_timestamp, b.Update_Timestamp as b_timestamp, c.Update_Timestamp as c_timestampFROM a,b,cWHERE a.ID = ? AND b.ID = a.id AND c.id = b.id



    Even by this query you are selecting the data only on the basis of ID, which is not really I need as I told you that I also need the record from the table with the latest Update_Timestamp field.

    I show what I am using right now...




    Now I am going to check that what is the value of flag.....
    for an example if it is ORC .. then I will fetch the record from Ora_received_Carrier table.....


    Another method I just invented is more complex and very big also I am also showing this method to ranchers.......

    Just confirm me that is there any other method which is more tuned then this one...
    Hope




    now I can make things more clear........

    Waiting for replies....
    Thankx in advance for reply....

     
    Shaan Shar
    Ranch Hand
    Posts: 1249
    Java Spring Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    What happen no updates till now, I am waiting still....


    Is there any other method .........


     
    Jeanne Boyarsky
    author & internet detective
    Marshal
    Posts: 34396
    346
    Eclipse IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Originally posted by Ankur Sharma:
    Thanks Stu,

    But that I know already but as you can see that there is a multiple hit on Database which is not good for performance tuning.....

    The method, earlier I explained is the other way and also prevent multiple hitting the Database I need some more better Query.




    I agree with Stu as I think the best approach is to use two queries. "multiple hit on Database which is not good for performance tuning". This isn't always true. I've seen really complex queries that took longer because the database had to do so much extra work to avoid doing a separate query.

    If you are concerned about network traffic, you can do the two queries in a stored procedure.
     
    Shaan Shar
    Ranch Hand
    Posts: 1249
    Java Spring Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Jeanne Boyarsky,

    So what do you think about my methodology what I have stated there in my earlier threads. If I write this whole code in Stored Procedures. Then which will be the best option for this sort of operations..

     
    Jeanne Boyarsky
    author & internet detective
    Marshal
    Posts: 34396
    346
    Eclipse IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Ankur,
    Can you provide a link to the previous thread? You have over 200 posts, which is too many to look though to find a specific one.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic