Member Avatar for Tunnleram

I know that I can limit a query by 10 for instance, and have the top rows returned, but I have a situation where I want to return all rows, but I want to be able to mark the rows that fall in the top 10 a certain way.

For instance, let's say we have the following tables

table: fruit
id int 11
name vchar 100

table: persons
id int 11
name vchar 100

table: owned_fruit
id int 11
fruit_id int 11
person_id int 11

I want to show a listing using the owned fruit table, show a count grouped by friend, but also highlight the fruit that is most popular while sorting alphabetically.

I'm just not sure what the best way to go about this is.

Any help much appreciated

  • 3 Contributors
  • 6 Replies
  • 198 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Tunnleram
Member Avatar for LastMitch

I want to show a listing using the owned fruit table, show a count grouped by friend, but also highlight the fruit that is most popular while sorting alphabetically.

Is it possible that you can provide a query with the table?

Member Avatar for pritaeas

Try a UNION merging the top 10 rows with the rest.

Member Avatar for Tunnleram

Not sure how I'd go about using a union in this situition.

I was trying to use an IF statement, but it appears that the IN does not allow LIMIT.

So I can do this...

SELECT g.id, g.name as game, s.name as status, 
(SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id) as members, 
IF(g.name IN (SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id), '1', '0') as Popular
FROM games g, statuses s
WHERE g.status_id = s.id
ORDER BY game ASC

but not this...

SELECT g.id, g.name as game, s.name as status, 
(SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id) as members, 
IF(g.name IN (SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id LIMIT 10), '1', '0') as Popular
FROM games g, statuses s
WHERE g.status_id = s.id
ORDER BY game ASC

I think this might be supported on newer versions of MySQL. Not sure.

Either way, there's the query I'm messing with. Any ideas of doing the IF differently to get what I want?

Edited by pritaeas because: Added markdown.
Member Avatar for LastMitch

I was trying to use an IF statement, but it appears that the IN does not allow LIMIT.

In the future please highlight your query.

SELECT g.id, g.name as game, s.name as status,
(SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id) as members,
IF(g.name IN (SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id LIMIT 10), '1', '0') as Popular
FROM games g, statuses s
WHERE g.status_id = s.id
ORDER BY game ASC

You can change it to this:

SELECT COUNT(game_id) FROM persons_favorite_games pfg, persons p WHERE g.id = pfg.game_id and pfg.person_id = p.id IN (1,3,5) ORDER BY game ASC LIMIT 10;

You can used the UNION query like what pritaeas mention.

Member Avatar for pritaeas

Or you can try a self join of the original table with the top 10 of that table.

Member Avatar for Tunnleram

I did it like this:

SELECT g.id, g.name as game,
(SELECT COUNT(*) from persons_favorite_games WHERE game_id = g.id) as members, 
CASE WHEN g.id IN (SELECT g.id FROM games g JOIN (SELECT game_id FROM persons_favorite_games pfg GROUP BY game_id ORDER BY COUNT(*) DESC LIMIT 25) pop ON g.id = pop.game_id) THEN 1 ELSE 0 END as popular 
FROM games g
Edited by pritaeas because: Added markdown.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.