how to produce random output in SQLite after union two tables

Question

I have a SQLite Database and I need to join two tables together and output the combined one in random order.

and the database is like this

enter image description here

I'm new to SQLite and after searching and reading tutor, I use the following command:

SELECT Column1 AS Eng,Column2 AS Chn
FROM list1
UNION  
SELECT Column1 AS Eng,Column2 AS Chn
FROM list2
ORDER BY Eng RANDOM()

But the output is

sqlite3.OperationalError: near "RANDOM": syntax error

However if I drop RANDOM() function it turns out to be nothing wrong except no random output

SELECT Column1 AS Eng,Column2 AS Chn
FROM list1
UNION  
SELECT Column1 AS Eng,Column2 AS Chn
FROM list2
ORDER BY RANDOM()

And if I drop Eng the output is wrong and the message is:

SELECT Column1 AS Eng,Column2 AS Chn
FROM list1
UNION  
SELECT Column1 AS Eng,Column2 AS Chn
FROM list2
ORDER BY Eng

the message is

sqlite3.OperationalError: 1st ORDER BY term does not match any column in the result set

I followed the instructions here Select random row(s) in SQLite and here Selecting a Random Row from an SQLite Table

Thank you!

Why is the `Eng` in the ORDER BY clause? You don't want to sort by `Eng`, do you? — CL., Mar 23 '15 at 13:12
Sorry ,i misunderstood your question. YEP, I want to output in random order, by if i left Eng the output will be wrong and the message is `1st ORDER BY term does not match any column in the result set` — sikisis, Mar 23 '15 at 13:23
@CL. Randomly and I edit my reply ,sorry did not catch your meaning first. — sikisis, Mar 23 '15 at 13:29

score 2 · Answer 1 · answered Aug 07 '19 at 23:27

Since like QL mentioned you must order by a column in your query you have to take a different approach. Random() is just a function.

So, instead of ordering by RANDOM(), you can select RANDOM(), and then order by it.

SELECT Column1 AS Eng, Column2 AS Chn, RANDOM() As Random
FROM list1
UNION  
SELECT Column1 AS Eng, Column2 AS Chn, RANDOM() As Random
FROM list2
ORDER BY Random

score 1 · Accepted Answer · answered Mar 23 '15 at 13:34

When you are using UNION, expressions in the ORDER BY clause are restricted to columns of your query. (Why? Because the SQL standard says so.)

You have to make the values to be sorted by a column of your query:

SELECT this, that, random() AS r
FROM ...
UNION ALL
SELECT what, ever, random()
FROM ...
ORDER BY r

how to produce random output in SQLite after union two tables

2 Answers2