Sorting of histograms


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

I think you are experiencing this issue - go and upvote by clicking :+1:

@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.

When is this problem expected to be resolved?

It didn’t work, have you tried it?Can you sort this successfully?Seeking screenshots and sql statements

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.

@beta No one knows. But I would guess it has low priority compared to some of the 2000 other issues.

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 tried some SQL to make this works. Odd results.

This is the original question. The sort is 'working' on the sum of sales by owner.

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	


That's just for a single table query!! If I were doing this with anything more complex, I'd use a view.

3 Likes

Your suggestion is very useful, thank you very much for your help, although the problem has been solved, but I think this should be a bug?

It’s down as an enhancement request.
I just enjoy writing awkward SQL!