How to ind only one record with UNION ALL in multiple tables

byMR

February 18, 2022

Sorry for the title of the question…

There are multiple records with the same data_name in different tables and with different time_inserts.

I need data_name with older time_insert. I cannot JOIN but only UNION ALL

data_name is type VARCHAR2 (255 BYTE)
time_insert is type TIMESTAMP (6)

This query works but is slow. I think it is not performing

select data_name,token,time_insert,path from table_0 where (data_name,time_insert) in 
(select data_name, min(time_insert) from table_0 where data_name like '%data_name_to_search%' group by data_name) 
union all 
select data_name,token,time_insert,path from table_1 where (data_name,time_insert) in 
(select data_name, min(time_insert) from table_1 where data_name like '%data_name_to_search%' group by data_name)
union all
select data_name,token,time_insert,path from table_2 where (data_name,time_insert) in 
(select data_name, min(time_insert) from table_2 where data_name like '%data_name_to_search%' group by data_name)

>Solution :

A common practice is to number the rows using ROW_NUMBER() and then pick only rows numbers 1.

The code below UNION ALLs the data together, then filters the data_name and numbers the rows, then picks only rows numbered 1.

SELECT
  table_id, data_name, token, time_insert
FROM
(
  SELECT
    unioned.*,
    ROW_NUMBER() OVER (PARITITION BY data_name ORDER BY time_insert) AS rn
  FROM
  (
    SELECT 0 AS table_id, data_name, token, time_insert, path FROM table_0
    UNION ALL
    SELECT 1 AS table_id, data_name, token, time_insert, path FROM table_1
    UNION ALL
    SELECT 2 AS table_id, data_name, token, time_insert, path FROM table_2
  )
    unioned
  WHERE
    data_name like '%data_name_to_search%'
)
  sorted
WHERE
  rn = 1

The table_id is unnecessary, but helps with debugging, testing, etc.