How to Sort the record for an odd sequence in sql server 2016

Question

How to make single record from the record-set at the bottom of the list.

0. Select All
1. Apple
2. Banana
3. Mango

I need to place "Select All" at the bottom as :

1. Apple
2. Banana
3. Mango
0. Select All

Based on Id, not value .

Possible duplicate of [How to keep a specific row as the first result of a query (T-SQL)?](https://stackoverflow.com/questions/10176538/how-to-keep-a-specific-row-as-the-first-result-of-a-query-t-sql) — Tab Alleman, Mar 05 '18 at 15:11
Is your question trying to ***imply*** that your data is two columns; `(id, value)`? If so, perhaps it would be better to actually show that, with column names? — MatBailie, Mar 05 '18 at 15:28
The solution in the duplicate question can be used whether you base it on id or value. — Tab Alleman, Mar 05 '18 at 15:59

score 3 · Accepted Answer · answered Mar 05 '18 at 15:11

You should union a calculated sort field with a value of -ALL- with your main table and then sort the results by the calculated field.

SELECT
    *
FROM
(
    SELECT
       RowOrder=2,
       RowValue='-ALL-'
    UNION
    SELECT
       RowOrder=1,
       RowValue=FruitTable.Value
    FROM
       FruitTable
)AS X
ORDER BY
   X.RowOrder,
   x.RowValue

Pawan Kumar · Answer 2 · 2018-03-05T15:53:54.770

2

Please try like this.

SELECT [Values] from 
(
    SELECT 'Select All' [Values] UNION ALL
    SELECT 'Apple' UNION ALL
    SELECT 'Banana' UNION ALL
    SELECT 'Mango' 
)u
ORDER BY Case [Values] WHEN 'SELECT All' THEN 1 ELSE 0 END,[Values]

edited Mar 05 '18 at 15:53

answered Mar 05 '18 at 15:10

Pawan Kumar

1,991
10
12

Up-voted, but could significantly improve on : formatting, alias names, and choice of the value `2000000` *(just use `1`)*. – MatBailie Mar 05 '18 at 15:23
@MatBailie - Thanks. I have updated the answer as per the suggestions. – Pawan Kumar Mar 05 '18 at 15:54

score 2 · Answer 3 · answered Mar 05 '18 at 15:19

similar to other answers, but I assume you understand the method:

    ;WITH T (List, Sort) AS 
    (
    SELECT 'Apple' , 1
    UNION ALL
    SELECT 'Banana' ,1 
    UNION ALL 
    SELECT 'Mango' ,1 
    ),
    T2 (List , Sort) AS 
    (
    SELECT * 
    FROM T
    UNION ALL
    SELECT 'Select All'  , 2
    )
    SELECT List 
    FROM T2
    ORDER BY Sort

How to Sort the record for an odd sequence in sql server 2016

3 Answers3