Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the date) has to be returned. I.e. if we have 5 entries, 2 of which have the _position 2, it should return only the latest one (based on a date field).

The current SQL I have been able to generate through WordPress is the following:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
  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 = '_position' AND CAST(wp_postmeta.meta_value AS UNSIGNED) > '0'
    ) )
  GROUP BY wp_postmeta.meta_value+0
  ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

Now that works fine for the most part, except, I'm getting weird results. The results below are grouped by wp_posts.ID (as WordPress does by default). The fields below are in format: Position: Title - date - time. Note date and time are saved as one field.

1: Title - August 10, 2012 - 10:24 am
1: Title - December 8, 2011 - 4:48 pm
2: Title - August 16, 2012 - 12:56 pm
2: Title - August 7, 2012 - 10:08 am
11: Title - March 28, 2012 - 2:11 pm
12: Title - February 27, 2012 - 2:12 pm

However, when I group it by wp_postmeta.meta_value+0 it somehow doesn't get the right option for some of them as shown below.

1: Title - August 10, 2012 - 10:24 am
2: Title - August 7, 2012 - 10:08 am
11: Title - March 28, 2012 - 2:11 pm
12: Title - February 27, 2012 - 2:12 pm

What I need it to return is something like this:

1: Title - August 10, 2012 - 10:24 am
2: Title - August 16, 2012 - 12:56 pm
11: Title - March 28, 2012 - 2:11 pm
12: Title - February 27, 2012 - 2:12 pm

As you can see it returns the one with the latest date. I've tried swapping the order of the date to ASC but I'm getting the exact same results.

What is going wrong here and how can fix it?

Thanks in advance.

EDIT: The SQL used for the first results:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
  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 = '_position' AND CAST(wp_postmeta.meta_value AS UNSIGNED) > '0'
    ) )
  GROUP BY wp_posts.ID
  ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

Also, the tables are from wp, I've copied the structure to pastebin: http://pastebin.com/60tYev1P

Thanks again!

share|improve this question
You'll need to give us the exact SQL used for the two resultsets above. The one stated didn't generate the result given (where did the dates come from?). – Phil Aug 16 '12 at 14:53
Thanks for the answer. See the edits. Not quite sure what you mean with where the dates came from? – Hosh Sadiq Aug 16 '12 at 15:34
@HoshSadiq: There are dates in the data you say you want it to return. But you're selecting only an ID in your SQL. – Mike Sherrill 'Catcall' Aug 17 '12 at 0:34
Oh, my bad, it seems WordPress only selects the ID of each record first, then selects each of them individually at a later stage, which also selects the date. The results were shown through PHP rather than direct MySQL output. – Hosh Sadiq Aug 17 '12 at 14:09

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.