Custom ordering a 'case' in redshift

Hi! 

So previously my query was just a working SQL query, but for Periscope it needs to be redshift compatible. I've managed to make everything work, except for one thing. My bars are in a very unusual order, and I can't seem to order them right (see image). It should be 1, 2 or 3, 4 or 5, etc.

The relevant parts of code are this bit in the select:

SELECT count(*),
       CASE
          WHEN aantal_bestellingen = 1 THEN '1 bestelling'
          WHEN aantal_bestellingen > 1 AND aantal_bestellingen <= 3 THEN '2 of 3 bestellingen'
          WHEN aantal_bestellingen > 3 AND aantal_bestellingen <= 5 THEN '4 of 5 bestellingen'
          WHEN aantal_bestellingen > 5 AND aantal_bestellingen <= 7 THEN '6 of 7 bestellingen'
          WHEN aantal_bestellingen > 7 AND aantal_bestellingen <= 9 THEN '8 of 9 bestellingen'
          WHEN aantal_bestellingen > 9 THEN '10 of meer bestellingen'
       END as bestellingen
FROM
    (...)

Then it used to work with this part in the group by and order by:

group by aantal_bestellingen
order by (
    CASE bestellingen
    WHEN '1 bestelling' THEN 1
    WHEN '2 of 3 bestellingen' THEN 2
    WHEN '4 of 5 bestellingen' THEN 3
    WHEN '6 of 7 bestellingen' THEN 4
    WHEN '8 of 9 bestellingen' THEN 5
    WHEN '10 of meer bestellingen' THEN 6
    END
) ASC

But after switching to redshift, it didn't anymore. I've tried some things, read some other forums, and came to this:

group by aantal_bestellingen
order by (
    CASE
    WHEN "bestellingen" = '1 bestelling' THEN 1
    WHEN "bestellingen" =  '2 of 3 bestellingen' THEN 2
    WHEN "bestellingen" =  '4 of 5 bestellingen' THEN 3
    WHEN "bestellingen" =  '6 of 7 bestellingen' THEN 4
    WHEN "bestellingen" =  '8 of 9 bestellingen' THEN 5
    WHEN "bestellingen" =  '10 of meer bestellingen' THEN 6
    END
 ) ASC

But this also didn't work.

So, I have a bunch of graphs with these kinds of bars, and i'd love to custom order them, but I have no clue how. If anyone knows how I can achieve this, I'd love the help! Thanks

(I don't believe the rest of the code is relevant, and I didn't want to clutter the topic, but if anyone thinks it is relevant, i'd be happy to post the rest too! I'm sorry our database fields and names are in Dutch, hope this doesn't complicate things to much)

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Eva ! Thanks for reaching out, have you tried checking off "Preserve Sort Order" under the Legend submenu on your chart format tab?

    Reply Like
  • Hi Neha , thanks for your awnser.

    Yes, i've tried checking and unchecking this, but it makes no difference unfortunately.. 

    Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 7 mths ago
      • Reported - view

      Eva Feel free to reach out to Periscope chat support via the intercom icon in-app at the bottom left corner of your screen. A closer look at exactly how the chart is set up could inform how to enforce the ordering as desired!

      Reply Like
  • Hi Neha Kumar Thanks for the sugestion, the chat support did came up with the solution, so thats awesome. 

    It was not the chart set up, but the query itself. I figured maybe someone else might be looking for an answer to the same problem, so I'm posting the solution here for this reason, perhaps i'll help someone else:

    In stead of focussing on the ordering, I added another case to the SELECT:

     SELECT count(*),
           CASE
              WHEN aantal_bestellingen = 1 THEN '1 bestelling'
              WHEN aantal_bestellingen > 1 AND aantal_bestellingen <= 3 THEN '2 of 3 bestellingen'
              WHEN aantal_bestellingen > 3 AND aantal_bestellingen <= 5 THEN '4 of 5 bestellingen'
              WHEN aantal_bestellingen > 5 AND aantal_bestellingen <= 7 THEN '6 of 7 bestellingen'
              WHEN aantal_bestellingen > 7 AND aantal_bestellingen <= 9 THEN '8 of 9 bestellingen'
              WHEN aantal_bestellingen > 9 THEN '10 of meer bestellingen'
           END as bestellingen
    , case
        when bestellingen = '1 bestelling' then 1
        when bestellingen = '2 of 3 bestellingen' then 2
        when bestellingen = '4 of 5 bestellingen' then 3
        when bestellingen = '6 of 7 bestellingen' then 4
        when bestellingen = '8 of 9 bestellingen' then 5
        when bestellingen = '10 of meer bestellingen' then 6
      end as orders
    
    FROM
        (...)

    After this, I simply ordered by 'orders':

    group by aantal_bestellingen
    order by orders

    After fine tuning the chart set up, It looked just the way I wanted it, so thanks Adam from support!

    Reply Like 1
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 7 mths ago
      • Reported - view

      Eva Thank you so much for sharing the solution! And hats off to Adam Luba 😄

      Reply Like
Like2 Follow
  • Status Answered
  • 2 Likes
  • 7 mths agoLast active
  • 5Replies
  • 43Views
  • 2 Following