CODEWITHSUNDEEP
mysqlsql
Chilion
Chilion

Reputation: 4490

Order a Group BY

There are alot questions on this topic, still can't figure out a way to make this work.

The query I'm doing is:

SELECT `b`.`ads_id`     AS `ads_id`,
  `b`.`bod_bedrag`      AS `bod_bedrag`,
  `a`. `ads_naam`       AS `ads_naam`,
  `a`.`ads_url`         AS `ads_url`,
  `a`.`ads_prijs`       AS `ads_price`,
  `i`.`url`             AS `img_url`,
  `c`.`url`             AS `cat_url`
FROM `ads_market_bids`  AS `b`
INNER JOIN `ads_market` AS `a`
ON `b`.`ads_id` = `a`.`id`
INNER JOIN `ads_images` AS `i`
ON `b`.`ads_id` = `i`.`ads_id`
INNER JOIN `ads_categories` AS `c`
ON `a`.`cat_id`       = `c`.`id`
WHERE `i`.`img_order` = '0'
AND `b`.`u_id`        = '285'
GROUP BY `b`.`ads_id`
HAVING MAX(b.bod_bedrag)
ORDER BY `b`.`bod_bedrag` ASC

But, the problem I keep seeing is that I need b.bod_bedrag to be sorted before the GROUP BY is taking place or so. Don't know how to explain it exactly.

The bod_bedrag i'm getting now are the lowest of the bids in the table. I need the highest.

Tried like everything, even tought of not grouping by but using DISTINCT. This didn't work either. Tried order by max, everything I know or could find on the internet.

Image 1 is the situation without the group by. Order By works great (ofc). No Group By Image 2 is with the group by. As you can see, the lowest bid is taken as bod_bedrag. I need the highest. With Group By

Upvotes: 1

Views: 116

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35583

One approach is to simulate row_number() (which MySQL does not have), but it allows for selection - by record - rather than by aggregates which may come from disparate source records. It works by adding to variables to each row (it does not increase the number of rows) Then, using an ordered subquery those variables are set to 1 for the highest b.bod_bedrag for each b.ads_id, all other rows perb.ads_id` get a higher RN value. At the end we filter where RN = 1 (which equates the the record containing the highest bid value) 

SELECT *
FROM (
       SELECT
           @row_num :=IF(@prev_value=`b`.`ads_id`, @row_num + 1, 1) AS RN
          ,`b`.`ads_id` AS `ads_id`
          ,`b`.`bod_bedrag` AS `bod_bedrag`
          ,`a`.`ads_naam` AS `ads_naam`
          ,`a`.`ads_url` AS `ads_url`
          ,`a`.`ads_prijs` AS `ads_price`
          ,`i`.`url` AS `img_url`
          ,`c`.`url` AS `cat_url`
          , @prev_value := `b`.`bod_bedrag`
       FROM `ads_market_bids` AS `b`
       INNER JOIN `ads_market` AS `a`     ON `b`.`ads_id` = `a`.`id`
       INNER JOIN `ads_images` AS `i`     ON `b`.`ads_id` = `i`.`ads_id`
       INNER JOIN `ads_categories` AS `c` ON `a`.`cat_id` = `c`.`id`
       CROSS JOIN
          ( SELECT @row_num :=1
             , @prev_value :=''
          ) vars
       WHERE `i`.`img_order` = '0'
       AND `b`.`u_id` = '285'
       ORDER BY `b`.`ads_id`, b`.`bod_bedrag` DESC
       )
WHERE RN = 1;

You can even turn off that silly GROUP BY extension, details in the man page: MySQL Extensions to GROUP BY

Upvotes: 1

Arth
Arth

Reputation: 13110

Judging by your output you want:

  SELECT amb.ads_id,
         MAX(amb.bod_bedrag) max_bod_bedrag,
         am.ads_naam,
         am.ads_url,
         am.ads_prijs ads_price,
         ai.url img_url,
         ac.url cat_url
    FROM ads_market_bids amb 
    JOIN ads_images ai 
      ON ai.ads_id = amb.ads_id
     AND ai.img_order = 0
    JOIN ads_market am 
      ON am.id = amb.ads_id 
    JOIN ads_categories ac
      ON ac.id = am.cat_id 
   WHERE amb.u_id = 285 
GROUP BY amb.ads_id, 
         am.ads_naam,
         am.ads_url,
         am.ads_prijs,
         ai.url,
         ac.url
ORDER BY max_bod_bedrag ASC

I have also removed all the unecessary backtickery and aliasing of columns to the same name. Your HAVING was doing nothing as all the groups 'have' a MAX(amb.bod_rag).

Upvotes: 3

Conffusion
Conffusion

Reputation: 4475

select distinct `b`.`ads_id` as `ads_id`, max(`b`.`bod_bedrag`) as `bod_bedrag`, 
`a`.`ads_naam` as `ads_naam`, `a`.`ads_url` as `ads_url`, `a`.`ads_prijs` as `ads_price`,
`i`.`url` as `img_url`, `c`.`url` as `cat_url` 
 from `ads_market_bids` as `b` 
   inner join `ads_market` as `a` on `b`.`ads_id` = `a`.`id` 
   inner join `ads_images` as `i` on `b`.`ads_id` = `i`.`ads_id` 
   inner join `ads_categories` as `c` on `a`.`cat_id` = `c`.`id` 
 where `i`.`img_order` = '0' and `b`.`u_id` = '285' 
 group by b.ads_id, a.ads_naam, a.ads_url, a.ads_prijs, i.url, c.url

Upvotes: 1

Related Questions