Menu Close Log in Get started

Lesson

SQL trick: ordering bars in a chart

A simple trick to preserve the order you want the bars to appear in a chart.

Here’s the problem: you’re writing a query, and you want to keep the results sorted on a bar or funnel chart, but the values the query returns screw up the ordering.

For example, let’s say you want to order something that doesn’t sort very well, like if you had four different steps labeled “First”, “Second”, “Third”, “Fourth” and wanted to sort those in their semantic order, regardless of whatever their corresponding values are. Metabase (or whatever tool you’re using) would sort those values as strings (i.e., they would get sorted alphabetically, not semantically, which wouldn’t make much sense: “First”, “Fourth”, “Second”, “Third”).

Here’s a trick for rearranging the chart to specify the order you want.

  1. Write your query however you’re going to write it (following best practices, of course).
  2. Assuming you want to order by the values in a column called step, at the end of the query, use a CASE expression to define the order for the values in the step column.
order by  
    CASE 
        WHEN step = 'First' then 1
        WHEN step = 'Second' then 2
        WHEN step = 'Third' then 3
        WHEN step = 'Fourth' then  4
    End

Example of sorting using a CASE expression

<em>Fig. 1</em>. Using a CASE expression to enforce the order of bars on a chart.
Fig. 1. Using a CASE expression to enforce the order of bars on a chart.

Here’s an example that uses the Sample dataset included with Metabase that you can try out for yourself. Let’s say we want to see the number of orders per product category, but we need to sort them like so: Widget, Gizmo, Gadget, Doohickey. Here’s the code with the case statement:

-- We want to return two columns, ordered by products.category
SELECT products.category,
       Count(*)
FROM   orders
       LEFT JOIN products
              ON orders.product_id = products.id
GROUP  BY products.category
-- The CASE statement will assign a new value to sort by
ORDER  BY CASE
            WHEN products.category = 'Widget' THEN 1
            WHEN products.category = 'Gizmo' THEN 2
            WHEN products.category = 'Gadget' THEN 3
            WHEN products.category = 'Doohickey' THEN 4
          END 

This trick is especially useful with funnel charts when you need to preserve the sequence.

Thanks for your feedback!

Get articles like this one in your inbox every month