Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. Dibbs - Member

   I have two tables (live and archive) and I need to return the latest value, (which may be in the live or the archive). Can someone please give me some pointers as to how I could do this? Oracle DB BTW.

   Posted 10 months ago #
2. allthepies - Member

   Not enough info.

   Presumably live and archive share the same structure but the data contents vary ?

   And are the following statements correct ?

   1. if an entry exists in live then it's the most recent and the one you want regardless of any archive entry
   2. if an entry doesn't exist in live then the archive entry is the one you want

   Posted 10 months ago #
3. Dibbs - Member

   Both statements are correct.

   Posted 10 months ago #
4. neillys75 - Member

   Depends on what the value you want is? (number, date, string)
   You could union 2 selects of everything to create a view that you then group to give you the MAX value (if its a number or date) from the view.

   As all the pies said, not really enough info to help, but hope that helps a bit!

   Posted 10 months ago #
5. allthepies - Member

   As above, use a UNION operator to select from both live and archive and then get the latest row using a MAX clause on a timestamp column (assuming you have one to reflect the "freshness" of each row)

   Posted 10 months ago #
6. llama - Member

   allthepies

   Posted 10 months ago #
7. Dibbs - Member

   I'm at home at the moment and only have limited access to the data, its a string that I need to return, this is what I'm using to return the string (controlentries) from the live table:-
   SQL13=SingleRecord, SELECT * FROM %ActiveOperatorLog% WHERE ControlEntries Like '%Chemistry%' Order By LogDate DESC

   I'm trying to retrieve controlentries from OperatorArchive if they're not in the ActiveOperatorLog.
   I hope this makes more sense.

   Posted 10 months ago #
8. sam_underhill - Member

   what allthepies said

   edit: Although union all is quicker (but may produce dupes) in SQL server, can't remember if that's ansi sql or available in Oracle.

   Posted 10 months ago #
9. joemarshall - Member

   Do you mean
   A)trying to retrieve a single latest record from either live or archive
   b) trying to receive all latest records from both tables where one column says the record type and another is the timestamp?

   If a) then just write the two selects (ordered by time desc, live table first), union them and limit 1 to make only one record be returned. Should even be nice and only scan the live table if it finds it there.

   If it is b) then I would be thinking of selecting max timestamps from both tables, then selecting the records relating to those timestamps.

   Posted 10 months ago #
10. oli82 - Member

    I'm reasonably sure you can union all in Oracle, the dupes shouldn't matter if there is going to be a max anyway.

    Something to bear in mind - that LIKE clause will be pretty slow, especially with the wild card at the front AND back. If you can possibly constrain it to 'Chemistry%' it'll perform a lot better...

    Posted 10 months ago #
11. 16stonepig - Member

    I was going to say just union it, then select the max, but there are some tidier ideas up there, especially if you've got indexes on the date columns.

    An aggregate of a union of two aggregate subqueries. This stuff gets me excited. I nearly exploded the other day when I got to do a self-join on a self-subquery to a join to another self-join. Had to think in about 6 dimensions.

    Posted 10 months ago #
12. HTTP404 - Member

    I'm trying to retrieve controlentries from OperatorArchive if they're not in the ActiveOperatorLog. I hope this makes more sense

    Here is my interpretation.
    Although a UNION or UNION ALL may well suit - it looks to me like you are after a single records answer.
    If you are after a record from either table as a minimum then you will have to add a UNION ALL to the select statement below and query the ActiveEntries.

    Using wildcard searches on columns disables indexes. Your ControlEntries column looks like a key column which may cause problems if you are accessing large *volumes* of data.
    So if you can use a clause like - ControlEntries = 'Chemistry'

    SELECT OA.*
    FROM %OperatorArchive% OA
    WHERE OA.ControlEntries LIKE'%Chemistry%'
    AND NOT EXISTS
    (SELECT 1 FROM %ActiveOperatorLog% AOL
    WHERE AOL.ActiveEntries LIKE '%Chemistry%'
    AND AOL.ActiveEntries = OA.ControlEntries)

    Posted 10 months ago #
13. toby1 - Member

    This page should blow your mind then

    Analytic functions

    Like searching suggests a badly designed data structure - check the table for something more practical that is indexed.

    Posted 10 months ago #
14. Dibbs - Member

    Thanks for the replys I'll have a play and let you know how I get on.

    Posted 10 months ago #

RSS feed for this topic

Reply

You must log in to post.