Mysql联合订单不工作[重复]

Question

This question already has an answer here:

• How to order by with union 8 answers

This is the query I am running:

SELECT * FROM ( 
    (SELECT wp_posts.*, wp_postmeta.meta_value AS views FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( wp_postmeta.meta_key = 'views' ) GROUP BY wp_posts.ID) 
UNION 
    ( SELECT wp_2_posts.*, wp_2_postmeta.meta_value AS views FROM wp_2_posts INNER JOIN wp_2_postmeta ON ( wp_2_posts.ID = wp_2_postmeta.post_id ) WHERE 1=1 AND wp_2_posts.post_type = 'post' AND (wp_2_posts.post_status = 'publish' OR wp_2_posts.post_status = 'private') AND ( wp_2_postmeta.meta_key = 'views' ) GROUP BY wp_2_posts.ID)
) AS posts ORDER BY views DESC LIMIT 0, 10

I want the entire result set to be ordered by views but what I seem to be getting back is the subqueries ordered. There are 4 posts in total 2 are returned from the first query and 2 are returned from the second query. The posts from the first query have 8 views and 7 views. The posts from the second query have 13 views and 0 views. So the order returned should go 13, 8 7, 0 but instead I'm getting 8, 7, 13, 0

Where am I going wrong?

</div>

Answer 1

Ah ok I solved it. It's because the views field isn't a numeric field so its ordering them as a string not a number! So because 13 begins with a 1 it comes after 8 and 7.