- SQL query (totally out of my depth)
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 9 years ago
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 entryPosted 9 years ago
2. if an entry doesn’t exist in live then the archive entry is the one you want
Both statements are correct.Posted 9 years ago
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 9 years ago
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 9 years ago
allthepiesPosted 9 years ago
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.Posted 9 years ago
I hope this makes more sense.
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 9 years ago
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 9 years ago
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 9 years ago
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 9 years ago
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.*Posted 9 years ago
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)
This page should blow your mind then 🙂
Like searching suggests a badly designed data structure – check the table for something more practical that is indexed.Posted 9 years ago
Thanks for the replys I’ll have a play and let you know how I get on.Posted 9 years ago
The topic ‘SQL query (totally out of my depth)’ is closed to new replies.