How to specify the order of data displayed in BigQuery using the Union All function

strugglingGuy 0
Viewed 36

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.

sql-order-by google-bigquery sql
2 Answers

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
nbk0

Try changing the order in the select

Instead of:

Select ErrorCode AS `Code`, ErrorDate AS `Date`

try:

Select ErrorDate AS `Date`, ErrorCode AS `Code`
Gibrán Vazquez0
Related