Avatar of ankurdk
ankurdk
 asked on

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 ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
ankurdk

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Peter Chan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gottler

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

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

ASKER
Appreciate the reply.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ankurdk

ASKER
Thanks all for all your valuable replies.