Select query with specific pattern, show specific record at 1st place

Question

Query:

SELECT * FROM station

Output:

id | stationname | total
-------------------------
 1 | Khar        | 5
 2 | SantaCruz   | 3
 3 | Sion        | 2
 4 | VT          | 1
 5 | newFort     | 3
 6 | Bandra      | 2
 7 | Worli       | 1
 8 | Sanpada     | 3
 9 | Joe         | 2
10 | Sally       | 1
11 | Elphiston   | 2
12 | Currey Road | 1

Is it possible to display all the record but with a condition that will display particular record at 1st position and below then display rest all records

E.g. my where condition is

where id=10

Desired output:

id | stationname | total
-------------------------
10 | Sally       | 1
 1 | Khar        | 5
 2 | SantaCruz   | 3
 3 | Sion        | 2
 4 | VT          | 1
 5 | newFort     | 3
 6 | Bandra      | 2
 7 | Worli       | 1
 8 | Sanpada     | 3
 9 | Joe         | 2
11 | Elphiston   | 2
12 | Currey Road | 1

Any idea, hint will be appreciated

You could try order by column = 10. Havent tried it my self. — Poul K. Sørensen, Nov 24 '12 at 10:46
@hims056: thanks for reply, well its a requirement in my project and even am not sure who to achive this, so i asked here — Satinder singh, Nov 24 '12 at 10:48
@s093294:thnks for rpely , order by work for asec or desc, here i need different requirement — Satinder singh, Nov 24 '12 at 10:50
I will check when at a pc, but you saying you cant order by a column? And if you can order by a column- i would assume sql could handle a expression in the order clause. Ect order by column1+ column2 asec — Poul K. Sørensen, Nov 24 '12 at 10:57

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

You can union two different query using UNION ALL like this:

SELECT * FROM Station WHERE ID=10
UNION ALL
SELECT * FROM Station WHERE ID<>10

See this SQLFiddle

Update (related to returned order is undefined)

If you see the execution plan of both queries (using UNION and UNION ALL), the query using UNION will sort the result while the query using UNION ALL will not sort the result. see execution plan of both queries in this SQLFiddle.

Execution Plan using UNION query:

enter image description here

Execution Plan using UNION ALL query:

enter image description here

I found this from SQL SERVER – Introduction and Example of UNION and UNION ALL

edited May 23 '17 at 11:56

Community

1
1

answered Nov 24 '12 at 10:48

Himanshu

31,810
31
111
133

1

Thank you, +1 for using sql fiddle – Satinder singh Nov 24 '12 at 10:54
A note of warning: This *might* fail because the [returned order is undefined](http://stackoverflow.com/questions/421049/sql-server-union-what-is-the-default-order-by-behaviour). – Lieven Keersmaekers Nov 24 '12 at 11:03
@LievenKeersmaekers - Look at [OP's answer](http://stackoverflow.com/a/634196/1369235) `UNION ALL` will stop sorting. – Himanshu Nov 24 '12 at 11:05
@hims056 - I missed that answer but OP neglects to give any reference. My gut feeling tells me you are right though. +1 on your answer until proven wrong :) – Lieven Keersmaekers Nov 24 '12 at 11:08
thanks to both of you, both query solved my problem, and i am using union all – Satinder singh Nov 24 '12 at 11:10
1

@hims056 - the +1 stands as your answer is correct for OP's problem but bypassers might want to read the conversation and warnings expressed in [comments](http://stackoverflow.com/a/13540545/52598). – Lieven Keersmaekers Nov 24 '12 at 12:14

Lieven Keersmaekers · Answer 2 · 2012-11-24T14:31:36.700

3

Using a UNION ALL could work in the case presented by OP but comes with some pitfalls that might lead to unexpected results when used indiscriminately.

A searched sort order alleviates these problems, provided that an id can be choosen to be guaranteed the first in the list (0 in this example)

SELECT * 
FROM   Station 
ORDER BY
       CASE WHEN id = 10 THEN 0 ELSE id END

See this SQLFiddle (cudo's to hims056 for setting it up)

edited Nov 24 '12 at 14:31

answered Nov 24 '12 at 10:59

Lieven Keersmaekers

57,207
13
112
146

@satindersingh - Thank you. I might have added that the `THEN` clause should contain the lowest possible `id` value but I assume that speaks for itself. – Lieven Keersmaekers Nov 24 '12 at 11:05
@LievenKeersmaekers - Your solution is better. – Himanshu Nov 24 '12 at 11:11
1

@hims056 - it might. Time will tell ;) – Lieven Keersmaekers Nov 24 '12 at 11:15
In free time will definetly do rearch on this which one is better, soon you get to know :) – Satinder singh Nov 24 '12 at 11:17
1

@LievenKeersmaekers - In the execution plan of both queries (using `UNION` and `UNION ALL`), query using `UNION` will sort the result while query using `UNION ALL` will not sort the result. see execution plan of both queries [here](http://sqlfiddle.com/#!3/b0f65/15). I found it from [this article](http://blog.sqlauthority.com/2008/10/15/sql-server-introduction-and-example-of-union-and-union-all/) – Himanshu Nov 24 '12 at 11:32
1

@hims056 - I finally taken the time to test one and another. For this particular case it works but to prove a point *(yes I **am** that evil)*, I have altered the statements a bit and added a clustered index **without altering the insert order**. You will notice that the results are now returned not on insert order but on clustered index order. All in all, you can trust on the UNION ALL behaving the way you expect but you have to keep in mind all those little things that might throw off your results. [SQL Fiddle Demo](http://sqlfiddle.com/#!3/dc0ce/1) – Lieven Keersmaekers Nov 24 '12 at 12:04
This is the correct answer. The Union method may not produce the expected sort all the time. – Sam Anwar Nov 24 '12 at 19:22

Select query with specific pattern, show specific record at 1st place

2 Answers2

See this SQLFiddle