Is there a better way instead of using multiple UNION ALL queries in SQL?-CodePudding

I want to run the same select query but change only the romecode field which is a string and limit each result by 30. Finally, I concatenate all the results into one usingUNION ALL.

This is the full list of codes which means that I need to repeat the same select UNION ALL many times:

('G1603', 'E1205', 'D1101', 'N1202', 'M1501', 'G1402', 'I1401',
'M1607', 'J1102', 'C1201', 'M1801', 'I1203', 'I1604', 'M1705',
'H2102', 'M1203', 'K2503', 'E1103', 'N1103', 'M1805', 'H1204',
'M1602', 'D1106', 'M1707', 'C1501', 'M1701', 'G1101', 'J1302',
'C1103', 'E1401', 'J1201', 'H1301', 'C1301')

And how I am doing now:

(
        SELECT
        appellationlibelle,
        romelibelle,
        romecode,
        descriptioncleaned,
        description
      FROM
        `scrappers-293910.vigilant_memory_raw.indeed` 
      WHERE romecode = 'G1603' LIMIT 30)

      UNION ALL
(
      SELECT
        appellationlibelle,
        romelibelle,
        romecode,
        descriptioncleaned,
        description
      FROM
        `scrappers-293910.vigilant_memory_raw.indeed` 
      WHERE romecode = 'E1205' LIMIT 30)

      UNION ALL
(
      SELECT
        appellationlibelle,
        romelibelle,
        romecode,
        descriptioncleaned,
        description
      FROM
        `scrappers-293910.vigilant_memory_raw.indeed` 
      WHERE romecode = 'D1101' LIMIT 30)

I repeat this select 33 times. I tried to find a similar solution but I couldn't find any. If it is a duplicated question just kindly drop the link please :D

CodePudding user response：

Combine into a single query, use Row_number() to give each row a number resetting on each Romecode. Then bring back all rows where the row number is 30 or below.

You just need to clarify how you choose which 30 rows to bring back. Your original query doesn't specify so you'll need to figure that out to plug into the row_number order by.

select
*
from
(
    SELECT
        appellationlibelle,
        romelibelle,
        romecode,
        descriptioncleaned,
        description,
        row_number() over (partition by romecode order by datecreation) as rn
      FROM
        scrappers-293910.vigilant_memory_raw.indeed
      WHERE romecode in
      ('G1603', 'E1205', 'D1101', 'N1202', 'M1501', 'G1402', 'I1401',
'M1607', 'J1102', 'C1201', 'M1801', 'I1203', 'I1604', 'M1705',
'H2102', 'M1203', 'K2503', 'E1103', 'N1103', 'M1805', 'H1204',
'M1602', 'D1106', 'M1707', 'C1501', 'M1701', 'G1101', 'J1302',
'C1103', 'E1401', 'J1201', 'H1301', 'C1301')
) thedata where thedata.rn <= 30

CodePudding user response：

Maybe I've misunderstood your question, but this seems easy to do with an IN (...) condition.

SELECT
        appellationlibelle,
        romelibelle,
        romecode,
        descriptioncleaned,
        description
      FROM
        `scrappers-293910.vigilant_memory_raw.indeed` 
      WHERE romecode in ('G1603', 'E1205', 'D1101', 'N1202', 'M1501', 'G1402', 'I1401',
'M1607', 'J1102', 'C1201', 'M1801', 'I1203', 'I1604', 'M1705',
'H2102', 'M1203', 'K2503', 'E1103', 'N1103', 'M1805', 'H1204',
'M1602', 'D1106', 'M1707', 'C1501', 'M1701', 'G1101', 'J1302',
'C1103', 'E1401', 'J1201', 'H1301', 'C1301')

-- LIMIT 30 <-- don't know if you still want this?