Using LIMIT in MySQL to limit results based on column value (PHP/MySQL)

Question

I did multiple searches for an answer to this (on SO and elsewhere), but haven't found an answer that really fit my needs (if it's out there, I apologize in advance).

I have a query, using PHP, that returns an array from a database (WordPress). Basically what I want to do is look at a column's value, and then LIMIT based on that value. Here's the array that's returned for a better idea:

http://pastebin.com/AC043qfh

In the query, you'll notice that the value for post_parent repeats for several of returned arrays. What I want to do is have it LIMIT to 3 based on the post_parent value e.g. I want 3 entries for post_parent 79, 87, 100, etc.

I'm not well versed (see: at all) in MySQL queries, but this is what I have to get that array:

SELECT DISTINCT ID, guid, post_parent, post_title 
FROM $wpdb->posts p 
WHERE p.post_type = 'attachment'
    AND p.post_mime_type LIKE 'image/%'
    AND p.post_status = 'inherit'
    AND p.post_parent IN
        (SELECT object_id FROM $term_relationships WHERE term_taxonomy_id = $post_term)

I've tried using GROUP BY, but that didn't get me what I wanted. Any help is appreciated.

EDIT Just to clarify, these are the results I want: http://pastebin.com/pWXdUuXv

Could you explain the rules to your limit? I mean, I can give you a query that will limit it to three, but that doesn't sound like what you want. — Justin Wood
– Justin Wood, Commented Sep 13, 2013 at 15:59
@Justin Wood He did - "I want 3 entries for post_parent 79, 87, 100, etc." — SamV
– SamV, Commented Sep 13, 2013 at 16:00
I'd recommend creating a "rank" and then use a conditional based on that rank... Changing LIMIT would require dynamic SQL at the least. — user645280
– user645280, Commented Sep 13, 2013 at 16:01
@Sam and like I said, I can create a query that can limit it to three, but he wants it based off of that field, which means it may not ALWAYS be a limit of three. — Justin Wood
– Justin Wood, Commented Sep 13, 2013 at 16:02
There's a tag for this: greatest-n-per-group. Try searching that tag. — Barmar
– Barmar, Commented Sep 13, 2013 at 16:26

score 4 · Accepted Answer · 2013-09-13 18:16:56Z

4

This might do the trick: (I'm assuming ID is unique, if not substitute something that is)

SELECT
  p.ID, guid, post_parent, post_title
FROM (
SELECT
  a.ID as ID,
  COUNT(*) as rank
FROM (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS a
JOIN (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS b ON b.ID <= a.ID AND b.post_parent = a.post_parent
GROUP BY a.ID
) AS r
JOIN $wpdb->posts p ON r.ID = p.ID AND r.rank <= 3
WHERE p.post_parent IN (
  SELECT object_id FROM $term_relationships
  WHERE term_taxonomy_id = $post_term)
GROUP BY p.ID
;

EDIT: Attempt to include category in rank so it'll actually work.

Specifying conditions twice is a bit ugly, but I didn't see an easy way around it.

edited Sep 13, 2013 at 18:16

answered Sep 13, 2013 at 16:45

user645280

Sign up to request clarification or add additional context in comments.

2 Comments

disinfor Over a year ago

Yeah, ID is unique to each image in the DB. post_parent is unique to the post that the image appears on, but obviously some images will share the same post_parent value (considering the question at hand). I tried running this as is and I didn't get any results, however, I'll take a look at modifying it as I see what you are getting at doing.

disinfor Over a year ago

ebyrob your edit did the trick and does exactly what I wanted to do. Thank you!

Collectives™ on Stack Overflow

Using LIMIT in MySQL to limit results based on column value (PHP/MySQL)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related