MySQL JOIN和MAX

Question

Hi I've got a query which does what I want to by displaying reviews which are rated first then followed by reviews which have yet to be rated. However I can't seem to get it to order correctly.

What the result should look like:

5
4
0
0
...

At the moment it is doing this:

4
5
0
0
...

Here is my query

$sql = $db->query( "
    SELECT branch.*, MAX(review.rating) AS m
    FROM branch
    LEFT OUTER JOIN review ON branch.bid = review.bid
    WHERE branch.address2 LIKE '$query' OR branch.postcode LIKE '$query-%'
    GROUP BY branch.bid
    ORDER BY m DESC, branch.branch ASC
    LIMIT $start,$limit 
" ) or die( "Select failed: (" . $db->errno . ") " . $db->error );

Answer 1

Sub-query solution by Meghraj Choudhary should work.

However, Joins are faster. Sub-queries require additional disk accessing. Assuming branch.bid is a primary key the following should be faster:

SELECT b1.*, b2.m FROM branch b1
INNER JOIN
(
SELECT branch.bid, MAX(review.rating) AS m
FROM branch
LEFT OUTER JOIN review ON branch.bid = review.bid
WHERE branch.address2 LIKE '$query' OR branch.postcode LIKE '$query-%'
GROUP BY branch.bid
LIMIT $start,$limit
) b2 ON
b1.bid = b2.bid
ORDER BY b2.m DESC, b1.branch ASC

I have not tried this. So, please try it and post back.