• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sql Query Need Help

 
Srilakshmi Vara
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys,

I have a table in which a field is priority holds HIGH, MED and LOW.

I need to sort this priority wise i.e., HIGH,MED,LOW.

If i use order by its not coming anybody have solution for this

Thanks in Advance,
Srilakshmi
 
Phillip Koebbe
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I try to avoid storing strings like that in the table. I prefer to use a "type table" and use referential integrity. For example, I would have a table called t_Priority that would look like this:



then in your table in question, have a PriorityId field foreign keyed to t_Priority. Then you can sort by PriorityId ASC or DESC and join over to t_Priority only when you need the textual description. Another thing that I often do is add a column called SortOrder to my type tables that can be used to control how the choices are sorted in drop down lists and such in the UI. You could also use a column like that to control precedence in your priorities (or any other "type").

Now, to try to answer your question, "How can I sort the data that I have?" If you are using a database that supports CASE, you could try something like



That should work. I don't have time to try it right now. I've made some pretty good use out of CASE statements when I've had to.

Peace,
Phillip
[ July 01, 2005: Message edited by: Phillip Koebbe ]
 
Phillip Koebbe
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Amidst grilling brats and dealing with disobedient children, I managed to give my idea a test and it works like I thought it would. Well, on MySQL 4.1.11 anyway.

Peace,
Phillip
 
Rick Portugal
Ranch Hand
Posts: 243
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In Oracle you could do something like this:

order by decode(priority,'HIGH',1,'MED',2,'LOW',3)

But I agree that the best solution is to just make the priority a number in the database.
 
Srilakshmi Vara
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks everybody, it worked thanks for all of your suggestions
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
maria,

Please statrt a new thread for your problem, this thread already have some ongoing discussion and does not have any resemblance to your problem,continuing your problem will either lead to closing this thread or moving to another forum,which will stop ongoing discussion

thanks
Shailesh
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic