Mysql error: invalid combination of sorts for operation "UNION"

Question

I am Boxonix!

I'm currently doing a comment section for my site, and Im a bit in trouble! This "Invalid combination of sorts for operation" UNION "appears when I run this query:

SELECT * FROM comments JOIN users ON comments.user_id = users.id ORDER BY users.id DESC LIMIT $LIMIT1 UNION SELECT * FROM comments JOIN videos ON comments.video_id = videos.id 

I'm alredy a little bit confused, I often don't use mysql! Please, help!

Answer 1

You can force sorting in the SELECT part of the query. For example, if the videos.comment column is utf8_general_ci and the comments.comment column is utf8_unicode_ci , you can make it be the same:

 SELECT comment COLLATE utf8_general_ci FROM comments UNION SELECT comment FROM videos 

But this is of limited use in case the sortings cannot be translated exactly as you want.

Answer 2

To avoid the "Invalid Sort Combination for UNION Operation" Mysql error, you must ensure that columns with the same index have the same sort or encoding type.

Answer 3

The problem is that the two subqueries will have different sets of columns.

The first subquery will provide you with all columns from comments and all columns from users . The second subquery will provide you with all columns from comments and all columns from videos . If the two users and videos tables do not have the same column definitions, then UNION will not work.

You need to decide which columns you need from each query, and then tune each SELECT ... so that they match.

Answer 4

You cannot use order by statement before concatenation. If you want to use first, you need to convert the result as a temporary table. then say the order as below

 select * from (SELECT * FROM comments JOIN users ON comments.user_id = users.id UNION SELECT * FROM comments JOIN videos ON comments.video_id = videos.id) as t order by id DESC LIMIT 1 

It’s important to note that both users and videos must have the same number of columns in the comment table. Because if you use union or union all , you must accept the equal value no. columns with the same data type.

If comments, users, videos have a different number of columns, then the user does not select * from. instead, select all columns as shown below.

If there are user_id, col1, col2, col3 columns in the comment table, then

 select user_id,col1,col2,col3 from (SELECT c.user_id,col1,col2,col3 FROM comments c JOIN users u c.user_id = u.id UNION SELECT c.user_id,col1,col2,col3 FROM comments c JOIN videos v ON c.video_id = v.id) as t order by user_id DESC LIMIT 1 

Answer 5

Replace $LIMIT1 with 1 and execute ORDER BY after the second query. Try the following:

 (SELECT * FROM comments JOIN users ON comments.user_id = users.id) UNION (SELECT * FROM comments JOIN videos ON comments.video_id = videos.id) ORDER BY id DESC LIMIT 1 

Note: The join operation requires that each of your two queries have the same number of columns in their result set.