Default order of a select query

ankurdk
ankurdk used Ask the Experts™
on
Hi,
I have a very simple doubt. Say for e.g, I have the following table, Employee :

Name      ID      Salary
A              1      14500      
B              2      25100
C              3      20000
D              4        9000
E              5        7500

and I run the below query :

select * from Employee (nolock) where Salary in(14500,25100,20000,9000)

My questions :

1) How would the order of the result set of the above query be decided WITHOUT an ORDER BY CLAUSE.

2) Can I control the order of the output in the order in which the parameters are passed in the "in" clause ? i.e I want the record with salary value "14500" to appear first followed by 25100 and so on ?

Knowledge sharing would be appreciated. :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Problem resolver
Commented:
To order the recordset in a specific order, you can put

...
order by case Salary when 14500 then 1 when 25100 then 2 when 20000 then 3 ... end

Open in new window

Commented:
1. The order of the Select is determined based on the order of the rows inserted into the table.

CREATE TABLE TMP1(COL1 INT)

INSERT INTO TMP1(COL1) SELECT 3
INSERT INTO TMP1(COL1) SELECT 4
INSERT INTO TMP1(COL1) SELECT 45
INSERT INTO TMP1(COL1) SELECT 12
INSERT INTO TMP1(COL1) SELECT 90
INSERT INTO TMP1(COL1) SELECT 31

SELECT * FROM TMP1 WHERE COL1 IN (90,3,12

Open in new window


2. You cannot do that. Insert the data the way want it to be ordered in the select.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
1) How would the order of the result set of the above query be decided
In that example only, it is likely to be the order of the clustered index. The real point is you cannot rely (and should not rely) on the "default order". For more complex queries involving multiple tables/joins etc. the default order gets much harder to predict.
2) Can I control the order of the output ... [by order of] parameters ... in clause?
No. If you require output in a particular order you should use ORDER BY in your query.

Author

Commented:
Appreciate the reply.

Author

Commented:
Thanks all for all your valuable replies.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial