The ordering of the histograms is not sorted in descending order
my sql is: SELECT oin.nick,oi.pay_price,CASE WHEN oi.pay_price<100 THEN 's' WHEN oi.pay_price<200 THEN 'm' ELSE 'l' END AS 'price' FROM (select * from orders where {{nick}} and {{create_time}} order by pay_price desc) oi inner join (select nick,sum(pay_price) as pp from orders where {{nick}} and {{create_time}} group by nick order by pp desc) oin on oin.nick=oi.nick
@beta Try putting the “order by pay_price desc” and “group by nick order by pp desc” at the end. If I’m reading this right you’ve got them in the subqueries and that doesn’t do a thing for what’s returned by the outer Select statement.
The SQL sort won’t sort in the order of the bar total, just in the order of the individual groups.
You can do what you need in SQL with subqueries, but it’s messy.
If you're trying to sort the order within a single histogram bar (column) I haven't faced this problem yet but I think it can be done with SQL alone.
To do something like what @flamber pointed out such as sort according to the height of each bar (column) within the histogram I haven't tried that precisely yet but like @AndrewMBaines indicated it can probably be done using SQL and I might just have the very thing you're looking for.
If you're trying to sort the histogram bars (columns) from left to right based on price category then have a look at the first screenshot and the SQL from my post below and adapt it to your needs (sort by height for example). Although the post has to do with custom bin-size for maps (GitHub Issue #9208), the histogram example provided may be just the thing you're trying to do. I worked really hard on this solution and it works marvelously well when embedding but I haven't tried it in the Metabase query builder interface (I rarely if ever use it). It's a pretty slick solution not just for sorting on custom text-labels but it is designed to give the embed user the ability to chose the price range and the bin-size too!
Note that I'm using MS SQL Server, it might need some tooling using MySQL, PostgreSQL, or some other SQL engine.
I changed the query to calculate the sales by country to allow sorting at a higher level. For some odd reason, it only sorted the first 50 groups. In SQL, it's all sorted correctly!
I tried various other methods (ROW_NUMBER etc) to change the sort, but it appears that Metabase ignores all those records without a value in the first group for the first section. Then ignores those without a value in the 2nd group for the 2nd section etc.
In the end the solution was this:
select s.Country, s.ContactPosition, sum(s.lastYearsSales) as sales,
(select sum(c2.lastYearsSales) from Customer c2 where c2.Country = s.Country) as CountryTotal
from
(
select c.Country, c.ContactPosition, c.LastYearsSales
from customer c
UNION
select distinct c.Country, c2.ContactPosition, 0
from customer c
cross join customer c2
) s
group by s.Country, s.ContactPosition
order by CountryTotal desc