Top Clause, order by clause

Question

5.00/5 (1 vote)

See more:

SQL

--Table Creation Statement
create table tbl_Employee
(
EmployeeID int primary key,
Name Varchar(3),
salary int 
);

--Insert Statements

insert into tbl_Employee values(1001,'ABC',10000);
insert into tbl_Employee values(1002,'EFG',10000);
insert into tbl_Employee values(1003,'MGM',20000);
insert into tbl_Employee values(1004,'MSM',30000);
insert into tbl_Employee values(1005,'XYZ',30000);
insert into tbl_Employee values(1006,'RST',40000);

--Query
select top(4) * from tbl_Employee order by salary

--Result Set
EmployeeID	Name	salary
1002	        EFG	10000
1001	        ABC	10000
1003	        MGM	20000
1005	        XYZ	30000

I have two questions regarding the result set produced here:-
i)Why is the Name 'EFG' coming before 'ABC'
ii)Why is the Name 'XYZ' is given preference over the Name 'MSM' in the result set produced although both are having the same salary.

I am using SQL Server 2008 R2.
Thanks in advance

Posted 3-Feb-12 20:28pm

puja11191

Updated 3-Feb-12 21:09pm

Amir Mahfoozi

v2

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

strogg · Accepted Answer · 2012-02-03T21:10:00

Solution 1

It's happening because you are not ordering by 'Name'. You cannot depend on the order unless you specifically order by the required column. Use

SQL

select top(4) * from tbl_Employee order by salary, Name

Comma separate the order by columns. This will order the results by Salary first, & then (for identical salaries) by Name

Posted 3-Feb-12 21:10pm

strogg

Comments

thatraja 4-Feb-12 3:38am

5!

OriginalGriff · Accepted Answer · 2012-02-03T21:11:00

Solution 2

1) Because you only specified salary in your ORDER BY clause: if you do not specify other fields, then SQL is at liberty to return them in any order it wishes. To change this, specify the field in the order of importance:

SQL

SELECT TOP(4) * FROM tbl_Employee ORDER BY salary, EmployeeID

2) See 1).

Posted 3-Feb-12 21:11pm

OriginalGriff

Comments

thatraja 4-Feb-12 3:38am

5!

Amir Mahfoozi · Accepted Answer · 2012-02-03T21:34:00

This is something that is depend on the execution plan that was created for this query and since you didn't specified any order for the Name column you told to the query optimizer that you don't mind about Name column and it can do whatever it wants with it.

And as I considered in this case your query :

SQL

select top(4) * from tbl_Employee order by salary

will produce the same result as this one :

SQL

select top(4) * from tbl_Employee order by salary , name desc

But we can not conclude that it adds name desc to the end of the query. Maybe this time it decided to do that but since we didn't mention anything about Name column order it can do whatever it wants.

To see the results without raising any question for you, run this one :

SQL

select top(4) * from tbl_Employee order by salary , name asc

Hope it helps.

Top Clause, order by clause

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Top Clause, order by clause

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us