Order by specific values in a column without using case statement

I would like to get the 开发者_C百科records in the below format:

if i have a record like

A, B, C, D 

and I would like get record in this order -

B, A, C, D, E, F, G, H, so on,

But I need the value B should be at the first row...

try this:

SELECT
    *, 1 AS SortBy 
    FROM YourTable
    WHERE YourCol='B'
UNION ALL
SELECT
    *, 2 AS SortBy 
    FROM YourTable
    WHERE YourCol!='B'
    ORDER BY SortBy, YourCol

You don't give any reason to not want to use CASE. I'd still give it a try and see which is faster, the UNION ALL or the CASE method:

SELECT
    *
    FROM YourTable
    ORDER BY CASE WHEN YourCol='B' then 1 ELSE 2 END, YourCol

EDIT Working example:

DECLARE @YourTable table (YourCol char(1), RowValue varchar(5))
INSERT @YourTable VALUES ('A','aaa')
INSERT @YourTable VALUES ('A','aa')
INSERT @YourTable VALUES ('B','bbb')
INSERT @YourTable VALUES ('B','bb')
INSERT @YourTable VALUES ('C','ccc')
INSERT @YourTable VALUES ('D','ddd')
INSERT @YourTable VALUES ('E','eee')
INSERT @YourTable VALUES ('F','fff')

SELECT
    *, 1 AS SortBy 
    FROM @YourTable
    WHERE YourCol='B'
UNION ALL
SELECT
    *, 2 AS SortBy 
    FROM @YourTable
    WHERE YourCol!='B'
    ORDER BY SortBy, YourCol

OUTPUT:

YourCol RowValue SortBy
------- -------- -----------
B       bbb      1
B       bb       1
A       aaa      2
A       aa       2
C       ccc      2
D       ddd      2
E       eee      2
F       fff      2

(8 row(s) affected)

SELECT * from mytable where mycolumn = "B";

followed by

SELECT * from mytable where mycolumn != "B" order by mycolumn asc;

Declare and populate table:

DECLARE @t TABLE (col1 CHAR)
INSERT @t
SELECT char(number+ 65)
FROM master..spt_values
WHERE type = 'P' AND number < 6 

Query1:

SELECT *, cast(ascii(col1)-66 as bit) * 2 + ascii(col1) [orderby]
FROM @t 
ORDER BY cast(ascii(col1)-66 as bit) * 2 + ascii(col1) 

Query2:

SELECT *
FROM @t 
ORDER BY replace(col1, 'B', ' ') 

Result for Query1: (the [orderby] column is included for documentation only)

col1 orderby
---- --------
B    66
A    67
C    69
D    70
E    71
F    72