• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Adding a LIMIT on a specific column in SELECT query

Hi All,

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)
group_id
creative_id

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
0
m175400
Asked:
m175400
1 Solution
 
imitchieCommented:
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
AND gd.id in (select top 3 group_id from creative_group cg1 where cg1.creative_id = c.creative_id order by pk_id)
ORDER BY g.status, c.boxed DESC, g.display_order, c.date_updated DESC, g.pk_id DESC

0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now