• This topic has 13 replies, 10 voices, and was last updated 9 years ago by Dibbs.
Viewing 14 posts - 1 through 14 (of 14 total)
  • SQL query (totally out of my depth)
  • Dibbs

    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.


    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


    Both statements are correct.

    Premier Icon neillys75

    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!


    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)

    Premier Icon llama



    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.

    Premier Icon sam_underhill

    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.

    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.


    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…

    Premier Icon 16stonepig

    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.


    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’

    FROM %OperatorArchive% OA
    WHERE OA.ControlEntries LIKE’%Chemistry%’
    (SELECT 1 FROM %ActiveOperatorLog% AOL
    WHERE AOL.ActiveEntries LIKE ‘%Chemistry%’
    AND AOL.ActiveEntries = OA.ControlEntries)

    Premier Icon toby1

    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.


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

Viewing 14 posts - 1 through 14 (of 14 total)

The topic ‘SQL query (totally out of my depth)’ is closed to new replies.