Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Order By special list, possible?


Hi,

I have a column with the values of 'Enabled', 'Paused' or 'Paused'. Is it
possible to sort it so that the 'Enabled' shows first, then 'Paused', and
then 'Disabled', and NOT alphabetically?

Thank you

ORDER BY CASE MyCol WHEN 'Enabled' THEN 1
                    WHEN 'Paused' THEN 2
                    WHEN 'Disabled' THEN 3 END

-----------------------------------------------Reply-----------------------------------------------

There are a couple of different ways you could do this.

--#1

ORDER BY CASE WHEN col = 'Enabled' THEN 1
              WHEN col = 'Paused' THEN 2
              WHEN col = 'Disabled' THEN 3
         END ;

The idea here is to create a integer column that maps to the desired
ordering value and sort by it.

-- #2

ORDER BY CHARINDEX( LEFT( col, 1 ), 'EPB' ) ;

The idea here is to find the position of the first character of the column
value in the string 'EPB' and sort by it.

--
Anith

-----------------------------------------------Reply-----------------------------------------------

Thanks! I learned something new!
"Anith Sen" <a@bizdatasolutions.com> wrote in message

news:OijeMiIqHHA.4496@TK2MSFTNGP06.phx.gbl...

Bruce Schwartz (fake_dont_send@anything_.com) writes:
> I have a column with the values of 'Enabled', 'Paused' or 'Paused'. Is it
> possible to sort it so that the 'Enabled' shows first, then 'Paused', and
> then 'Disabled', and NOT alphabetically?

Bien sr!

ORDER BY CASE col
              WHEN 'Enabled' THEN 1
              WHEN 'Paused' THEN 2
              WHEN 'Disabled 3
         END

--
Erland Sommarskog, SQL Server MVP, esq@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc