- SQL query (totally out of my depth)
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 8 years ago
2. if an entry doesn’t exist in live then the archive entry is the one you wantneillys75Subscriber
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 8 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 8 years ago
I hope this makes more sense.joemarshallMember
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 8 years agooli82Member
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 8 years ago16stonepigSubscriber
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 8 years agoHTTP404Member
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 8 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)
The topic ‘SQL query (totally out of my depth)’ is closed to new replies.