CODEWITHSUNDEEP
phpmysql
Hitu Bansal
Hitu Bansal

Reputation: 3137

Mysql limit with Group by

I have three tables:

  1. category
  2. buisness
  3. business_category( having field, id, category_id, buisness_id)

One Category Belong to Many Business
and One Business can have multiple Category

Now I am trying to do this and I want to get following results:

Category 1

buisenn 1
buisness 2
buisnesss 3


Category 2

business 1  for cat2
business 2 for cat 2
business 3 for cat 2

I want to show only three business for each category.

This is the query what I tried:

SELECT  `category`.`name` AS `category_name`,
        `category`.`id` AS category_id,
        `business`.`avg_service_price` AS avg_price,
        `business`.`name` AS `business_name`,
        `business`.`address`,
        `business`.`address2`,
        `business`.`city`,
        `business`.`state`,
        `business`.`zipcode`,
        `business`.`id` AS `business_id`,
        `business`.`lat`,`business`.`lon` 
    FROM `business_category`
    LEFT JOIN `business`
        ON `business_category`.`business_id` = `business`.`id` 
    LEFT JOIN `category`
        ON  `business_category`.`category_id` = `category`.`id`
    GROUP BY `business`.`id`
    LIMIT 0,3

It show only 3 results.

Can anybody give me idea?

Thanks

Upvotes: 0

Views: 86

Answers (2)

Rick James
Rick James

Reputation: 142298

You need the equivalent of "groupwise max". See my blog, which has this example:

SELECT
    province, n, city, population
FROM
  ( SELECT  @prev := '', @n := 0 ) init
JOIN
  ( SELECT  @n := if(province != @prev, 1, @n + 1) AS n,
            @prev := province,
            province, city, population
        FROM  Canada
        ORDER BY
            province   ASC,
            population DESC
  ) x
WHERE  n <= 3
ORDER BY  province, n;

Upvotes: 1

M0N0NE
M0N0NE

Reputation: 63

You can not do that. Limit is used just to get the 3 rows of your query and what you want is to get all the results but just 3 elements for each category.

A solution could be, getting all the categories at first and then create a query for each category with limit 3. Another solution could be getting all the results and then removing those that you don't want.

The first solution is slower but the second one needs more memory.

Upvotes: 1

Related Questions