Custom sorting in Amazon Redshift / SQL

Question

I'm writing some SQL queries where I need to sort the grouped values by abbreviated month names. I think I've tried all the possibilities I could found but still something is not working. I try to use the 'ORDER BY CASE' statement which was advised here but that doesn't work and all the time I get this error:

Request Rejected by Server TableauException: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42703] ERROR: column "months_strings" does not exist in vw_rs_incident_service_level_results

Here is an example of my query.

with filtered_table as (
    select slmdefinition, state, 
    breachedflag, mesurementstopdatelocal, cgaccount,
    case
      when extract(month from mesurementstopdatelocal) = 1 then 'Jan'
      when extract(month from mesurementstopdatelocal) = 2 then 'Feb'
      when extract(month from mesurementstopdatelocal) = 3 then 'Mar'
      when extract(month from mesurementstopdatelocal) = 4 then 'Apr' 
      when extract(month from mesurementstopdatelocal) = 5 then 'May'
      when extract(month from mesurementstopdatelocal) = 6 then 'Jun'
      when extract(month from mesurementstopdatelocal) = 7 then 'Jul'
      when extract(month from mesurementstopdatelocal) = 8 then 'Aug'
      when extract(month from mesurementstopdatelocal) = 9 then 'Sep'
      when extract(month from mesurementstopdatelocal) = 10 then 'Oct' 
      when extract(month from mesurementstopdatelocal) = 11 then 'Nov'
      when extract(month from mesurementstopdatelocal) = 12 then 'Dec'  
    end as months_strings
    from cdm_explorer_schema.vw_rs_incident_service_level_results
    where (state = 'completed' and slmdefinition like '%Resolution%' and 
    slmdefinition like 'L%')
    )

    select slmdefinition as slm_definition,
        sum(case when breachedflag = 'false' then 1 else 0 end) as 
        number_of_unbreachflaged,
        count(breachedflag) as number_of_all_incidents,
        number_of_unbreachflaged / number_of_all_incidents * 100 as ratio,
        months_strings as month,
        cgaccount as company_name
        from filtered_table
        group by company_name, slm_definition, month
        order by case
            when month = 'Jan' then 1
            when month = 'Feb' then 2
            when month = 'Mar' then 3
            when month = 'Apr' then 4
            when month = 'May' then 5
            end

I'm connecting to the Redshift via Tableau Online. Thanks in advance!

Update:

Salman A · Accepted Answer · 2022-05-10T10:57:52.307

0

I would rather use the month-number-to-month-name conversion at the very end. Something like this:

with filtered_table as (
    select foo, bar, extract(month from mesurementstopdatelocal) as month_number
    from ...
    where ...
)
select foo, bar, case month_number
    when 1 then 'Jan'
    when 2 then 'Feb'
    when 12 then 'Dec'
end as month_name
from filtered_table
group by foo, bar, month_number
order by month_number

edited May 10 '22 at 10:57

answered May 10 '22 at 09:33

Salman A

262,204
82
430
521

Thanks @Salman A. That works in that example but unfortunately I need to sort values with GROUP BY statement where I have few different columns and it that scenario your solutions doesn't work. I use WITH statement to build first query and base on that I take values that needs to be grouped and order. Can this or similar solution can be applied somehow or do I need to build the query from scratch? – kosekk_g May 10 '22 at 10:09
I dont see any group by in your question. – Salman A May 10 '22 at 10:13
that was just a simplified example of my problem. I updated my question with exact query. Thanks! – kosekk_g May 10 '22 at 10:52
thank you very much, that solves my problem! – kosekk_g May 10 '22 at 11:10

Custom sorting in Amazon Redshift / SQL

1 Answers1