ORDER BY 1

[ehenry]

Is there a method to ORDER BY a specific order. I have a table where I ORDER BY [Station ID]. I was wondering if there was a way to set the specific order of the stations.
Ex. ORDER BY [Station ID] (1234,1235,1236,1237,1239,1238).

Any help would be appreciated.

 

[bborissov]

Nope,
But you could use CASE:

ORDER BY CASE WHEN [Station ID] = 1238
              THEN 1
         ELSE 0 END, [Station ID]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

order by StationID + case when StationID = 1238 then 1 when StationID = 1239 then -1 else 0 end

not tested

 

[SkipVought]

Thanx Borislav Borissov ==> [purple]*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ehenry]

Yeah I ended up using Case, I had 25 stations that needed to be in a specific order so it was a pain but it works. Thank you.

 

[gmmastros]

I think I would have done this differently.

There is probably a station table where the StationId is unique. I would add a column to this table named SortOrder. Set the data in this column to match the order you want your stations to appear in.

Then, modify your query.

If you are not already joining to the station table, then add the join. Change the order by clause to:

Order By Station.OrderBy

The benefit with this approach is that you do not need to modify a query to change the sort order (and you can probably guarantee that someone will eventually want it changed). All you'll need to do is change some data in a table to change the sort order.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, this is much better idea. In the worse case scenario you can build this orders table on the fly with table variable.