ORDER BY an equal value in MySQL

Question

All, I've got the following SQL query as of now:

SELECT * FROM $wpdb->posts
JOIN $wpdb->term_relationships ON $wpdb->term_relationships.object_id=$wpdb->posts.ID
JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id=$wpdb->posts.ID
WHERE 
$wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'post'
AND $wpdb->term_relationships.term_taxonomy_id='$vendor_category_to_use'
AND $wpdb->postmeta.meta_key='zip'

I have a postmeta.meta_key called featured. Then the value of if the post is featured is in another column called postmeta.meta_value. If the meta_value = "yes" where the meta_key equals "featured" then I want to display this one first and then display the rest of the posts after that. How can I go about doing that?

EDIT: Here is the data setup:

postid       meta_key      meta_value
123          featured      yes
324          featured      no
182          featured      yes
873          featured      yes

So in this example I'd like my posts to be displayed in this order:

postid       meta_key      meta_value
123          featured      yes
182          featured      yes
873          featured      yes
324          featured      no

I hope that helps!

Here is the SQL that gets generated:

SELECT * 
FROM wp_posts 
JOIN wp_term_relationships ON wp_term_relationships.object_id=wp_posts.ID 
JOIN wp_postmeta ON wp_postmeta.post_id=wp_posts.ID 
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' AND wp_term_relationships.term_taxonomy_id='5' AND wp_postmeta.meta_key='zip' AND (wp_postmeta.meta_value = '46320' OR wp_postmeta.meta_value = '46321' OR wp_postmeta.meta_value = '46322' OR wp_postmeta.meta_value = '46323' OR wp_postmeta.meta_value = '46324' OR wp_postmeta.meta_value = '46327' OR wp_postmeta.meta_value = '46394' OR wp_postmeta.meta_value = '46402' OR wp_postmeta.meta_value = '46404' ) 
ORDER BY (wp_postmeta.meta_key='featured' AND wp_postmeta.meta_value='yes') DESC, wp_posts.post_date DESC

Thanks!

Answer 1

SELECT ...
FROM   ...
ORDER BY (meta_key='featured' AND meta_value='yes') DESC, postid ASC;

If (meta_key='featured' AND meta_value='yes') for a row, that row will have a 1/TRUE. Otherwise, it will have a 0/FALSE. Hence, sorting descending puts the rows that have TRUE first.

Answer 2

It is straightforward like this:

...
ORDER BY featured = 'yes'

featured = 'yes' is evaluates as boolean true or false, for items that featured = "yes" is true will come first then items that has featured = "yes" is false i.e featured = "no" come later.

Update:

You can use CASE statement in the order by clause to sort your data on a sepecific field, something like:

ORDER BY 
        CASE WHEN postmeta.meta_key = 'featured' 
             THEN meta_value = 'yes' DESC  
        END

OR:

ORDER BY ( postmeta.meta_key = 'featured' AND meta_value = 'yes') DESC

You might need to add another order by criteria in the ELSE part of the CASE statement to order the items that have meta_key = 'featured is false to order them by.

If the field metadata_key is always = "featured" then you can get rid of the case statement and use just ORDER BY meta_value = 'yes' DESC