0

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?

Steve Buzonas
  • 5,300
  • 1
  • 33
  • 55
geoffs3310
  • 5,599
  • 11
  • 51
  • 104
  • I am following the instructions from the post you have linked to but it isn't working, that's why I have posted this question to find out why – geoffs3310 Jan 19 '15 at 20:57
  • from what i recall the meta_value field is a string in the wordpress schema, cast it to an integer in your selects and sort should work – Steve Buzonas Jan 19 '15 at 21:11
  • 1
    Just missed the afterthought edit by a minute, `CAST(wp_postmeta.meta_value AS UNSIGNED) AS views` and the same in your union. – Steve Buzonas Jan 19 '15 at 21:20

1 Answers1

0

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.

geoffs3310
  • 5,599
  • 11
  • 51
  • 104