Adding new ROWID to SQLite SELECT statement based on the UNION of several tables

Question

Consider the following SQLite statement where each product_id may appear at least once in each table. The purpose of our SELECT statement being to merge all the product_id's into one table.

SELECT product_id
  FROM product_id_table_UK
UNION
SELECT product_id
  FROM product_id_table_DE
UNION
SELECT product_id
  FROM product_id_table_ES
UNION
SELECT product_id
  FROM product_id_table_IT
UNION
SELECT product_id
  FROM product_id_table_FR

How can one add an ID INTEGER column to the resulting view?

Each table has it's own ID INTEGER column but if we SELECT ID column then we will have duplicate product_id columns in the new view with separate ID's.

Addding ROWID doesn't work since it returns the IDs of the tables.

Furthermore views do not have rows stored in them.

Answer 1

This is painful in older versions of SQLite (pre-row_number() days). You can do:

with p as (
      SELECT product_id
      FROM product_id_table_UK
      UNION
      SELECT product_id
      FROM product_id_table_DE
      UNION
      SELECT product_id
      FROM product_id_table_ES
      UNION
      SELECT product_id
      FROM product_id_table_IT
      UNION
      SELECT product_id
      FROM product_id_table_FR
     )
select p.*,
       (select count(*) from p p2 where p2.product_id <= p.product_id)
from p;