how to order a mysql varchar field with assumed col values?

3 Views

How to order a varchar field with assumed date values:
eg: . 

February 2014, April 2014, Q1 2014, Q2 2014

I need to order the field date wise ascending(or descending) with assuming Q1=March, Q2=October,

Read More

so it should order like: 

February 2014, Q1 2014, April 2014,  Q2 2014
Post Views: 3

Related posts

Leave a Reply

3 comments

  1. CAST you VARCHAR values to DATETIME values then the bulit in mysql ORDER BY asc/desc will work properly. If you don’t have a day just set all the days to the 1st of the month. I have no idea what those Q values are but that is no date format that I have seen and I am pretty sure mysql won’t konw what to do with it either.

  2. Since you dont appear to have other alternatives identifying a standard sequencing, you might be able to get what you want with a structured order by case/when construct such as… Your quarters don’t make sense as March to October is more than 3 months apart… But lets assume you Financial calendar starts in Jan, so March is the last month of first quarter…

    order by
   case when YourField like 'Jan%' then '01'
   when YourField like 'Feb%' then '02'
   when YourField like 'Mar%' then '03a'
   when YourField like 'Q1%'  then '03b'
   when YourField like 'Apr%' then '04'
   when YourField like 'May%' then '05'
   when YourField like 'Jun%' then '06a'
   when YourField like 'Q2%'  then '06b'
   when YourField like 'Jul%' then '07'
   when YourField like 'Aug%' then '08'
   when YourField like 'Sep%' then '09a'
   when YourField like 'Q3%'  then '09b'
   when YourField like 'Oct%' then '10'
   when YourField like 'Nov%' then '11'
   when YourField like 'Dec%' then '12a'
   when YourField like 'Q4%' then '12b' end

    But a more efficient way might be to have a lookup table of what months / quarters represent the order you want… Then join to that. I have the “a” and “b” versions so if your data DID have both a March and Q1 entry, the March would be listed first, then Q1. You can’t use just a month numeric of the literal as Q would always sort at the end and not give you what you need.

    Also, if your Financial calendar does not start on Jan 1, then your first quarter might start such as Mar, Apr, May for Q1, Jun, Jul, Aug for Q2, etc..

    Also, I have the case/when values sorted as ’01’, ’02’, etc so as they will always be before ’10’, ’11’ and ’12’ entries.

  3. I have no DB where I could test this, nor do I have mysql installed so take this as more of pseudo code than an exactly syntactically correct solution but you may want to try something like this…

    SELECT *, CAST((CASE
    WHEN CONTAINS(dateField, 'Q1') THEN 'March' + SUBSTRING(dateField) 
    WHEN CONTAINS(dateField, 'Q2') THEN 'October' + SUBSTRING(dateField)
    ETC... 
    ELSE datefield) AS DATETIME) as Date1  
FROM mytable
ORDER BY Date1