sort results by highest performing country & region I want to check which country &amp; regions are performing the best in sales.
I have the following order table:

| order_id | amount |
|----------|--------|
| 100      | 1000   |
| 101      | 250    |
| 102      | 120    |
| 103      | 500    |
| 104      | 25     |
| 105      | 2000   |
| 106      | 300    |


And order_address table:

| order_id | country | region |
|----------|---------|--------|
| 100      | US      | CA     |
| 101      | US      | LA     |
| 102      | UK      | GL     |
| 103      | UK      | GL     |
| 104      | UK      | GB     |
| 105      | CA      | OT     |
| 106      | AU      | QL     |


I want to get orders data by country &amp; region and sort by highest performing country (region).

SELECT 
    oa.country, oa.region, SUM(o.amount) AS 'total_sales'
FROM order o
INNER JOIN order_address oa ON o.order_id = oa.order_id
GROUP BY oa.country, oa.region
ORDER BY SUM(o.amount) DESC, oa.country, oa.region 


But this is not working as expected as it's breaking the country sequence. Other country results should only follow if all regions of high performing countries are displayed.

In fact, here is the desired result:

| country | region | total_sales |
|---------|--------|-------------|
| CA      | OT     | 2000        |
| US      | CA     | 1000        |
| US      | LA     | 250         |
| UK      | GL     | 620         |
| UK      | GB     | 25          |
| AU      | QL     | 300         |
I'm assuming that you want the results sorted by (total by country), then by country (in case of a tie at this level), then by (total by region), then by region (if needed, as a tie-breaker at the region level).

Here's the adjusted SQL:

SELECT 
    oa.country, oa.region, SUM(o.amount) AS 'total_sales'
FROM `order` o
INNER JOIN order_address oa ON o.order_id = oa.order_id
INNER JOIN (SELECT oa1.country, SUM(o1.amount) AS ctry_sales
              FROM order_address oa1
                     INNER JOIN `order` o1 ON o1.order_id = oa1.order_id
             GROUP BY oa1.country
           ) c ON oa.country = c.country
GROUP BY oa.country, c.ctry_sales, oa.region
ORDER BY c.ctry_sales DESC, oa.country, SUM(o.amount) DESC, oa.region


We determine the total per country in the sub-query labelled c. We then join that to the two tables you already had, and add the per-country total as the first sort condition in the ORDER BY. When then move the country before the total sales by region, to make sure that we keep entries for the same country together, even if two countries have the same per-country total.

Results (as you can see in this SQLFiddle) are what you requested:

CA  OT  2000
US  CA  1000
US  LA  250
UK  GL  620
UK  GB  25
AU  QL  300