Adding a LIMIT on a specific column in SELECT query
Posted on 2007-12-02
OK, this one is a bit of a brain buster but I'm sure you lot will be able to spot it in no time. I've been looking at it for too long and need to step back for a moment.
The following query retrieves creative's (a creative is a user) from the database on a per-group (like a category) basis from table 'creative'.
Note: $desc is an integer relating to the category (or group) we want to know about.
$sql = "SELECT DISTINCT(c.id) FROM creative c, creative_group g, group_discipline gd WHERE c.id = g.creative_id AND g.group_id = gd.id AND ( gd.id = '$disc' OR gd.map_ids = '$disc' /* new bit - select people who are in 'child' groups too */ ) AND c.visible = 1 AND c.active = 1 ORDER BY g.status, c.boxed DESC, g.display_order, c.date_updated DESC, g.pk_id DESC";
It just allows us to find out how many users are within each category.
When a user registers, they specify the categories of interest to them. These are stored in 'creative_group' in order of relevance. So the user will select their primary categories of interest first. For the purpose of the query above, I want to retrieve the same data but only for users where the $desc group whatever that may be, was in their top 3 choices.
Table creative_group looks like this:
pk_id (Primary Key and AUTO INCREMENT)
Image the following data within creative_group
| pk_id | group_id | creative_id |
| 167 | 75 | 56 |
| 168 | 36 | 56 |
| 169 | 15 | 56 |
| 170 | 21 | 56 |
| 171 | 11 | 56 |
| 172 | 58 | 56 |
| 173 | 9 | 56 |
This tells us that user id 56 has shown interest in 7 categories (groups) and that group_id 75 is of most interest to them, followed by 36 and then that in which they are least interested, group_id 9. What I want to do with the query I gave, is make sure it would only return the above user id 56 if $desc was either 75, 36 or 15, as they were in said user's top 3 choices.
I hope you can help! Sticking thign into arrays and so on, if anyone wanted to suggest that, would be extremely difficult given the makeup of the existing site, which I did not build myself. Hence, a clever alteration to this SQL query itself, which I'm sure must be possible, would be much appreciated!
Hope to hear from you soon guys!
many thanks in advance!
SELECT creative_id FROM creative_group