Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
liamfitz
Starting Member
10 Posts |
 Posted - 2012-06-06 : 14:47:34
|
Can I set an 'ORDER BY' clause in my SQL select command, on a text-field to anything other than an alphabetical sort ( i.e A-z, or Z-A, ASC or DESC ) ? ljfitz |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-06-06 : 14:57:01
|
| not sure what you mean by that? You mean sort it if it's numeric otherwise no sort? |
 |
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-06-06 : 16:13:15
|
| [code]We can order the data on the basis of colum whether it has numeric valueor alphanumeric. select * from yourtable order by name We are not clear with your question.can you please tell us what do you need?[/code]Vijay is here to learn something from you guys. |
|
|
|
liamfitz
Starting Member
10 Posts |
Posted - 2012-06-06 : 18:11:14
|
| Thanks. I mean, I have for example, in a field called 'Status' four and only four, possible values. They are 1)'Open Priority', 2)'Open Standard', 3)'Waiting List', and 4)'Closed'. I wish to sort them in that order i.e. 1,2,3,4. However, as you know if you add an ORDER BY clause to a text field ie. in this case, ORDER BY tblReferral.Status ASC ( or DESC for that matter ), it sorts based on alphabetical order of the value i.e. 'Closed' would be 1st in 'ORDER BY ASc because C is before O and W, the other initials, and on a DESC sort, it would come out W,O,O,C. Neither the order I want. Makes sense ?ljfitz |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-06 : 18:24:29
|
| TryORDER BY CASE WHEN Status = 'Open Priority' THEN 1 WHEN Status = 'Open Standard' THEN 2 WHEN Status = 'Waiting List' THEN 3 WHEN Status = 'Closed' THEN 4 ENDJimEveryday I learn something that somebody else already knew |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-06-06 : 18:31:58
|
| Assuming that you have a Status domain table, you could add an "Ordinal" column to the table and then you can just sort on the column instead of having to hard code values in your code. |
|
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-06-06 : 18:58:25
|
| [code]select status,CASE WHEN Status = 'Open Priority' THEN 1WHEN Status = 'Open Standard' THEN 2WHEN Status = 'Waiting List' THEN 3WHEN Status = 'Closed' THEN 4 end as [Order] from #status ORDER BY [Order][/code]Vijay is here to learn something from you guys. |
|
|
|
|
|
|
|
|
ORDER BY on text fields ( non-alphabetical ) ?