Query of a view containing a max() from a union of queries appears to match internal values
Oracle Database 11g Release 11.2.0.2.0 - 64bit (SE)
I’m hoping someone can help or confirm the theory behind some behaviour we are seeing in (possibly not occuring on XE version).
We have a complicated view that “union all”s a number of subqueries in a CTE to produce two columns an ID and timestamp, within the view we then group by ID and MAX the timestamp to get the latest date for each ID. A (very) simplified pseudo-code illustration:
CREATE VIEW complex_view (id, latest_date) WITH query1(id,date) AS : 1, 1 Jan 1970 query2(id,date) AS : 1, 1 Jan 1980 SELECT merged.id, MAX(merged.date) FROM ( SELECT id, date FROM query1 UNION ALL SELECT id, date FROM query2 ) merged GROUP BY merged.id