I am using the Union all function in Bigquery to display data from different tables. Here is an example of what I am doing
(Select ErrorCode AS `Code`, ErrorDate AS `Date`
From Errors
Where id = x
Order By ErrorDate ASC)
UNION ALL
(SELECT 'Error Code', 'Error Meaning')
UNION ALL
(SELECT '1001', 'Restart your PC')
UNION ALL
(SELECT '1002', 'Check the cables')
What I am trying to achieve here is a table that displays all the error codes an account has encountered, and then at the bottom a simple print of all the error codes we have in our system and their meaning.
Now, when I run this code in BigQuery, for some reason the error codes and their meanings are showing up first, then the codes the system encountered. Example of result I am getting:
Error Code | Error Meaning
1001 | Restart your PC
1002 | Check the Cables
------------------------------
1003 | 2022-09-12
1001 | 2218-09-04
I want the error codes the system encountered to show up first and then the meaning. Something like this
Code | Date
1003 | 2022-09-12
1001 | 2218-09-04
------------------------------
Error Code | Error Meaning
1001 | Restart your PC
1002 | Check the Cables
How can I specify this order in BigQuery? Moving the code portions up and down does not help.
Add another sorting column
(Select ErrorCode AS `Code`, ErrorDate AS `Date`,1 as _sort
From Errors
Where id = x
Order By ErrorDate ASC)
UNION ALL
(SELECT 'Error Code', 'Error Meaning',2)
UNION ALL
(SELECT '1001', 'Restart your PC',3)
UNION ALL
(SELECT '1002', 'Check the cables',4)
ORDER BY _sort
Qir´th a CTE it looks like
WITH CTE as (
(Select ErrorCode AS `Code`, ErrorDate AS `Date`,1 as _sort
From Errors
Where id = x
Order By ErrorDate ASC)
UNION ALL
(SELECT 'Error Code', 'Error Meaning',2)
UNION ALL
(SELECT '1001', 'Restart your PC',3)
UNION ALL
(SELECT '1002', 'Check the cables',4))
SELECT `Code`, `Date` FROM CTE
ORDER BY _sort