CODEWITHSUNDEEP
mysqldatabaseunionunion-all
girish
girish

Reputation: 301

ORDER BY not working properly with UNION in MySQL

I have 2 tables t1 and t2 as follows

enter image description here

enter image description here

I want to select the results from both the tables with a UNION and sort on qty column. The query that I have written is

(SELECT * FROM t1)
UNION ALL
(SELECT * FROM t2)
ORDER BY qty ASC;

That gives me the below result

enter image description here

Can some 1 tell me what is going wrong here? As per the query I should get qty column sorted in ascending order. I have also tried

(SELECT * FROM t1
UNION ALL
SELECT * FROM t2)
ORDER BY qty ASC;

No luck with that either. If I do sort with id or item it works perfectly fine. This is really frustrating. Please help.

Upvotes: 0

Views: 669

Answers (1)

Evangelica
Evangelica

Reputation: 26

Possible duplicate of Sorting varchar field numerically in MySQL

Try

(SELECT * FROM t1
UNION ALL
SELECT * FROM t2)
ORDER BY CAST(qty as SIGNED INTEGER) ASC;

Upvotes: 1

Related Questions