Order clause by own ordering method

[lsy]

I have a column name priority, in this column, the data consists "Very High, High, Medium, Low and etc"
how can i make a order clause acording to the Priority level?
i found from this from net, but is only applicable in SQL server... how can i modify this to suit mine?

SELECT * FROM TABLE ORDER BY COLUMN_X IN ('KK', 'AA', 'CC')

i want order by Very high, high, medium, low

 

[Atomic Shrimp]

I would create a separate table to describe the range of priority levels available - something like:

Level_ID: Autonumber
Level_Text: Text
Sorting order: Number (double)

so the table would look like this:
1, Very High, 0
2,High,1
3,Medium,2
4,Low,3

Then set up the main table to use this as a lookup for the priority field - storing the autonumber, but displaying the text.

Then you can join it in a query and sort by the sorting order field.

The reason for using the autonumber for the join and a separate sorting order for the sorting is that you may wish to insert a new level of priority at some point - and you won't be able to coerce the autonumber sequence to fit the new regime without upsetting existing data. After you've added a new level, your table will look like this:

1, Very High, 0
2,High,1
3,Medium,2
4,Low,3
5, Moderately High, 1.5

so the new sorting order - sorted by the third field - is:
Very High
High
Moderately High
Medium
Low

 

[Guus2005]

I was about to suggest the same as Mike; use a reference table. It has the advantage of adding more Priority's. You can change the priority on the fly. There is no chance that today you spell "Very High" and tomorrow "Very high" which means the same but is different.

If you don't want that, you can use the "switch" function as shown in my database sample.

Enjoy!

 

[Atomic Shrimp]

I was about to suggest the same as Mike; use a reference table. It has the advantage of adding more Priority's. You can change the priority on the fly. There is no chance that today you spell "Very High" and tomorrow "Very high" which means the same but is different.

Also, a reference table means users can't just invent their own priority levels as they see fit (i.e. 'Urgent', 'Important', 'Crucial', 'Mandatory', 'Vital', 'Trivial', etc) - because given the opportinity, they will.

 

[lsy]

I was about to suggest the same as Mike; use a reference table. It has the advantage of adding more Priority's. You can change the priority on the fly. There is no chance that today you spell "Very High" and tomorrow "Very high" which means the same but is different.

If you don't want that, you can use the "switch" function as shown in my database sample.

Enjoy!

how can i put default in the switch function?

 

[Guus2005]

If you want to use a default when the value is null, first run an update query

update table1 set field1=1 where field1 is null

table is the name of your table, field1 is the name of the field you are using.

You can also set the default of the field to 1 or whatever. Open the table in design mode and change the default property of the field.

Enjoy!