| Author |
Topic |
|
pizzojm
Starting Member
20 Posts |
 Posted - 2003-01-16 : 09:55:31
|
| I have a table of zones. Listed by number and name. I need to switch the order of two zones in my query. For example they are listed 1, 2, 3, 4.....,12,13,... I want it to return 1, 2, 3, 4...13,12,. Anyone have any idea if this can be done, and if so how?The sql so far is easy as it gets: SELECT zone_num, zone_name FROM tblzones...Thanks for any help on this! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-16 : 10:01:36
|
| I would add a "Sort" field to the zones table and use that field for sorting. Otherwise, you need to write a little ORDER BY CASE ... expression, but that would need to be changed as zones are added and removed or as the ordering needs change. Storing a field in the table makes maintence easier, and makes it easier for multiple views and stored procs to consistently sort this table as needed.- Jeff |
 |
|
|
pizzojm
Starting Member
20 Posts |
Posted - 2003-01-16 : 10:04:46
|
| That is the thing I probably should have included. Adding a new table or fields to the current zones table is not really an option. I needed to do this query with only what I have. Any examples on how the ORDER BY CASE would work in this situation? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-16 : 10:10:12
|
| SQL is a set based language. There really is not concept of row number, first row, last row, next row etc. You have a table. A table has rows. A row has columns. How the rows are physically stored and ordered on the disk is of no concern to you.That's why you have having trouble. SQL is very good at ordering (sorting) a given rowset by the values in their columns. But what you are asking to do here is to sort the rowset based on values (rules) that do not exist in columns, but rather, in your head (or business needs).Jeff, suggestion (which is a sound one) is to materialize those rules in the form of a column so that you can now sort on the values.EDIT: the rules can be materialized or computed so as to become a column in a derived table in a query. You don't need to ALTER the table to do this.(I hope I've added value to this thread. I hate it when someone comes behind a suggestion I have made and repeats the same suggestion. That is not my intention here, Jeff...I'm trying to abstract a bit so that pizzojm can understand why he got to this place.)Jay White{0}Edited by - Page47 on 01/16/2003 10:13:34 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-16 : 10:15:58
|
| Thanks for helping to explain my rationale for adding the field from another way of looking at it; combines the logical reasons for doing it with the physical reasons very nicely.Since you can't change any objects in the database, try:SELECT zone_num, zone_name FROM tblzonesORDER BYCASE WHEN zone_num = 13 THEN 12 WHEN zone_num = 12 THEN 13 ELSE zone_num END- JeffEdited by - jsmith8858 on 01/16/2003 10:16:31 |
|
|
|
pizzojm
Starting Member
20 Posts |
Posted - 2003-01-16 : 11:03:03
|
| This is what I was looking for all along --CASE WHEN zone_num = 13 THEN 12 WHEN zone_num = 12 THEN 13 ELSE zone_num END Thanks! Joe. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-01-16 : 15:32:52
|
| pizzojm, just a reminder about Jeff's caveat in his first post... If you add any more zones, you MIGHT have to expand that CASE statement.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-01-17 : 07:49:37
|
| Why does jsmith8858's solution workIsn't the result "ORDER BY 12" at some point.Which I thought was interpreted as a column position that dosen't exist?Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-17 : 08:00:48
|
| Sitka, good point.It seems that when a case statement is evaluated in the order by, sql assumes the literal rather than the column position.select n from numbers order by 2 ... fails.select n from numbers order by case when 1=1 the 2 end ... works.Jay White{0} |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-01-17 : 19:07:18
|
Thanks Page47, I really tried to figure this out but it wouldn't clear itself up. Then I got real paranoid, like little SQL gremlins  were gonna get me. Glad I got the courage to ask. Bringing the buggers into the light so to speak.Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
|
Reordering a Query