CODEWITHSUNDEEP
mysqlwordpress
and
and

Reputation: 53

MySQL (and WordPress): select rows containing the highest value in one column and order by another

This was not so hard before moving my data to WordPress but I'm not certain how to get the same results: a certain number of posts belongs together (like an issue of a magazine) with articles, ordered manually. I want to get all the articles from the latest issue and order them according to another field.

With my old tables I could do

select title, issue, num
from magazine
where issue = (
select max(issue)
from magazine
)
order by num

With WordPress, the data is split into two tables, one for posts (articles) and another for the metadata.

I can get all the articles of the latest issue with the following query:

select post_title, post_date from wp_posts 
where ID in (select post_id 
             from wp_postmeta 
             where meta_key = 'myissue' and meta_value = (select max(meta_value + 0) 
                                                          from wp_postmeta 
                                                          where meta_key = 'myissue'));

The question is: how do I order the result by another custom field?

Upvotes: 1

Views: 965

Answers (2)

and
and

Reputation: 53

I've found the solution:

select post_title, post_date, meta_value
    from wp_posts, wp_postmeta
    where ID in (
        select post_id
            from wp_postmeta
                where meta_key = 'myissue'
                and meta_value = (
                    select max(meta_value + 0)
                        from wp_postmeta
                            where meta_key = 'myissue'
                )
       )
    and ID = post_id
    and meta_key = 'myorder'
    order by meta_value + 0;

Upvotes: 1

core1024
core1024

Reputation: 1882

If your query does not return many results the easiest way is to use temporary table. The query would be something like this:

SELECT * FROM (
select post_title, post_date from wp_posts 
where ID in (select post_id 
             from wp_postmeta 
             where meta_key = 'myissue' and meta_value = (select max(meta_value + 0) 
                                                          from wp_postmeta 
                                                          where meta_key = 'myissue'))
) tmptbl ORDER BY post_date DESC;

Upvotes: 1

Related Questions