order by enumeration

Question

0.00/5 (No votes)

See more:

i just want to order the query out put using enumeration value {september, october, november, december}
so i write

select * from fee order by month {september, october, november, december}

since it will simply dispaly the record in alphabetic order i.e. starting from April, but since our calender begins from September i want it to be the first record to be displayed. can anyone help me? Thanks!

Posted 11-Aug-10 5:41am

getrelax

Add a Solution

2 solutions

Solution 1

I'm not sure what you're trying to achieve, but you try converting the months into integers and sorting on it like that.

Otherwise, you could create a table variable with the months and a second column indicating order. Join that up to your data and then order by the temporary tables order column.

Either should do it.

Posted 11-Aug-10 6:53am

Andrew Rissing

Updated 11-Aug-10 6:54am

v2

Comments

getrelax 11-Aug-10 13:04pm

i think as u said if i create another table with with 2 columns (i.e. an int field to sort as i wish, & month) and if i join it with the main table it will work. but i am interested to know weather i can give an enum value for sorting purpose of certain field. I think i have seen it before, is that possible?

Andrew Rissing 12-Aug-10 10:53am

I'm not aware of enum support for MS SQL, but other DB's may have it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AspDotNetDev · Accepted Answer · 2010-08-11T07:12:00

Solution 2

I'm not exactly sure the order you are going for, but you can do a custom ordering based on the month. You could do something like this:

SQL

SELECT
    *
FROM Fee
ORDER BY
    CASE Month
        WHEN 'September' THEN 1
        WHEN 'April' THEN 2
        -- ...
        ELSE 999
    END AS MonthOrder ASC

Not sure if the syntax is exactly correct, but you get the idea.

Posted 11-Aug-10 7:12am

AspDotNetDev

Updated 11-Aug-10 7:13am

v2

Comments

getrelax 11-Aug-10 13:32pm

i am using the code in vb6 and it generates the following error
Error 5000 : Error in processing report.
[ Extended Info:[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(case fee.month when 'september' then 1 when 'october' then 2 else 999 END AS MonthOrder ASC)'.]

AspDotNetDev 11-Aug-10 14:04pm

Didn't know you are using Access. It seems, in Access, you can make use of the Switch function rather than the Case statement: http://www.webcheatsheet.com/SQL/access_functions/switch.php

getrelax 12-Aug-10 9:45am

Thanks dear, it works perfect!