CODEWITHSUNDEEP
mysql
jeff
jeff

Reputation: 1

MYSQL query to find most popular entry

My table looks like:

hook_bait  | brand    | flavour
----------------------------------
corn       |          |
bread      |          |
boily      | variable | variable

I need to run a mysql query that will give me the name of the most popular hook_bait, and if a boily - what brand and flavour the most popular boily is.

I am not sure where to start

Upvotes: 0

Views: 1353

Answers (1)

Hck
Hck

Reputation: 9167

You can get the most popular hook_bait by query like this:

SELECT 
  hook_bait, 
  COUNT(hook_bait) AS popularity
FROM sample_table
GROUP BY hook_bait
ORDER BY popularity DESC
LIMIT 0, 1

Complete example query:

SELECT 
  hook_bait, 
  IF(hook_bait = 'boily', (SELECT brand FROM sample_table t2 WHERE t2.hook_bait = t1.hook_bait GROUP BY brand ORDER BY COUNT(brand) DESC LIMIT 1), '-') AS brand,
  IF(hook_bait = 'boily', (SELECT flavour FROM sample_table t2 WHERE t2.hook_bait = t1.hook_bait GROUP BY flavour ORDER BY COUNT(flavour) DESC LIMIT 1), '-') AS flavour
FROM sample_table t1
GROUP BY hook_bait
ORDER BY COUNT(hook_bait) DESC
LIMIT 0, 1

Upvotes: 4

Related Questions