CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
TarranJones
TarranJones

Reputation: 4242

mysql choosing groupby row

Ive seen some good answers to similar questions but i still cant seem to fix my problem . i have a table of called page_to_banner, which looks like this

page_id    banner_id   
1             1   
1             2    
2             1   
2             2  
2             3

when in the admin area, editing a page (page.php)

i would like to list all the banners which are connected to this page first, then after list all other banners.

But If I don't GROUP BY banner_id then the order is correct but i get multiple instances of the same banner. (obviously)

If I do GROUP BY banner_id although the banner may be connected to the current page, the row returned for each banner might not necessarily return that page id. there for will be listed after. 

$sql = " SELECT * FROM banner AS b ";
$sql.= " LEFT JOIN page_to_banner AS p2b on(b.banner_id=p2b.banner_id) ";
$sql.= " GROUP BY p2b.banner_id";
$sql.= " ORDER BY p2s.page_id ='" . $page_id . "' ,b.date_modified ";

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I don't think you need group by:

select b.*
from banners b
order by (exists (select 1
                  from page_to_banner p2b
                  where p2b.banner_id = b.banner_id and
                        p2b = $page_id
                 )
         ) desc,
         b.date_modified;

I would discourage using group by, because that just incurs additional overhead.

If page_to_banner has no duplicates, you can also do:

select b.*
from banner b left join
     page_to_banner p2b
     on p2b.banner_id = b.banner_id and p2b.page_id = $page_id
order by (p2b.page_id is null) desc, b.date_modified;

Upvotes: 1

Related Questions