Dan Dan - 2 months ago 8
MySQL Question

show only top 2 values in group

The current code I have finds the average and shows all 4. I am trying to trim it down to holy show the information for the top 2 highest average,

SELECT BRANCHSUBURB, AVG(SESSIONPRICE)
FROM BRANCH NATURAL JOIN SESSIONS
GROUP BY BRANCHSUBURB
ORDER BY BRANCHSUBURB;


i.e should only show manly and cronulla
click image
click me

Answer

Just use order by and limit:

SELECT BRANCHSUBURB, AVG(SESSIONPRICE)
FROM BRANCH NATURAL JOIN SESSIONS 
GROUP BY BRANCHSUBURB
ORDER BY BRANCHSUBURB
ORDER BY AVG(SESSIONPRICE) DESC
LIMIT 2;

Note: I strongly advise you not to use NATURAL JOIN. In my opinion, it is a bug waiting to happen. The NATURAL JOIN works based on any columns that have the same names in the two tables -- it doesn't even use declared foreign key relationships. It is much better to use a regular join with a USING clause.