Change order of ORDER

[djj55]

Hello, 2008R2
I hope this make since, say I have a column containing
ClientA
ClientB
ClientC
ClientD
ClientE
...
If I

SELECT myColumn FROM myTable ORDER BY myColumn

it will order as expected. Now say I want
ClientD
ClientB
ClientA
Others in their order, (ClientC,ClientE,ClientF,ClientG...).
Thus have a given order for three clients and not worry about the rest.

Is the best way to do this is use a "lookup" table where there is a column for controlling the ORDER BY?

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

In my opinion, yes. Having a lookup table is the easiest way to accomplish this. There is another method, but it requires you to hard code the data in the query (instead of a table).

Ex:

Declare @Temp Table(Data VarChar(20))
Insert Into @Temp Values('ClientA')
Insert Into @Temp Values('ClientB')
Insert Into @Temp Values('ClientC')
Insert Into @Temp Values('ClientD')
Insert Into @Temp Values('ClientE')

Select Data
From   @Temp
Order BY Case When Data = 'ClientD' Then 1
              When Data = 'ClientB' Then 2
              When Data = 'ClientA' Then 3
              Else 4
              End,
         Data

I do not recommend this approach because you need to change the query if you ever want to change the sort order.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Auguy]

Maybe this is too simplistic, but could you add a SortBy column? I have used this in some of my tables. It comes in handy for sorting things like customer names that start with "The" like "The Smith Company". The user would normally put "Smith Company" in the SortBy column. In the user interface I default the SortBy to be the same as the customer name and let the user change it if necessary.

Auguy
Sylvania/Toledo Ohio

 

[djj55]

Possible, the second table is about the same.
Thanks

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!