asked on 5/2/2014

Default order of a select query

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. :)

Microsoft SQL Server Microsoft SQL Server 2008

Last Comment

ankurdk

5/2/2014

ASKER CERTIFIED SOLUTION

Peter Chan

5/2/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Gottler

5/2/2014

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.

PortletPaul

5/2/2014

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.

ankurdk

5/2/2014

ASKER

Appreciate the reply.

ankurdk

5/2/2014

ASKER

Thanks all for all your valuable replies.