CONCEPT OF ORDER BY CLAUSE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CONCEPT OF ORDER BY CLAUSE

Author

Topic

lasha90
Starting Member

5 Posts

Posted - 2011-12-13 : 02:01:37

i made the following table and was trying the order by clause on it by specifying multiple column names..i dint kno the proper syntax of writing asc/desc after every column name.and got the following varied results:::---

this is the table named for_orderby:-

create table for_orderby
( rollno int primary key,
name varchar(20),
marks int,
addre varchar(20))

insert into for_orderby values(1,'abhi',20,'ggn')
insert into for_orderby values(2,'ankur',30,'saket')
insert into for_orderby values(3,'anshu',40,'nakrola')
insert into for_orderby values(4,'ayat',50,'delhi')
insert into for_orderby values(5,'nishtu',60,'kakrola')
insert into for_orderby values(6,'lola',70,'dwarka')

I WROTE THE FOLLOWING QUERY FOR ORDER BY:-
select * from for_orderby order by marks,name desc

AND GOT THE FOLLOWING RESULT:-

1 abhi 20 ggn
2 ankur 30 saket
3 anshu 40 nakrola
4 ayat 50 delhi
5 nishtu 60 kakrola
6 lola 70 dwarka

AFTER THIS I WROTE THE FOLLOWING SLIGHTLY DIFFERENT QUERY:-
select * from for_orderby order by name,marks desc

AND GOT THE FOLLOWING OUTPUT:-

1 abhi 20 ggn
2 ankur 30 saket
3 anshu 40 nakrola
4 ayat 50 delhi
6 lola 70 dwarka
5 nishtu 60 kakrola

CAN ANYONE PLZZ EXPLAIN ME THIS CONCEPT OF ORDER BY..HOW IS THIS CLAUSE WORKING ON THE COLUMN NAMES!!!!!!!!!!!!!!!!????????

edit: moved to proper forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 02:12:43

it will order based on columns you've specified in same sequence
so first case it starts ordering based on ascending order of marks then out of them it tries order based on names in descending order for them with same marks
second case it starts ordering based on ascending order of names then out of them it tries order based on marks in descending order for them with same name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-12-13 : 08:08:52

Hello ,I can explain it in another way ,
1- for your first case you order by marks then by name ,So engine look first for numbers 20,30,40,50,60,70 ,Then he look to alphapitical order if 2 number is equal ,by another word he give first periority to numbers the second for alphaptical orders
2- for second case vice versa

lasha90
Starting Member

5 Posts

Posted - 2011-12-13 : 08:43:28

thanks a lot johntech and visakh16. your explanations really helped in clearing my concept.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 08:52:16

quote:
Originally posted by lasha90

thanks a lot johntech and visakh16. your explanations really helped in clearing my concept.

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources