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

General SQL Server Forums

New to SQL Server Programming

CONCEPT OF ORDER BY CLAUSE???

Author

Topic

lasha90
Starting Member

5 Posts

Posted - 2011-12-13 : 02:32:33

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!!!!!!!!!!!!!!!!????????

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-13 : 02:35:18

All the columns are being ordered column by column however the second "ORDER BY column", marks, will only be sorted if two rows are identical in the first column (name) therefore with the current data you will see no effect using the ORDER BY on column on marks.

kfluffie
Posting Yak Master

103 Posts

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

Try this and see the difference:

CREATE TABLE #TABLE
(firstname char(50),
lastname char(50))

INSERT INTO #TABLE (firstname, lastname) VALUES ('Erik', 'Xyz')
INSERT INTO #TABLE (firstname, lastname) VALUES ('Erik', 'Abc')
INSERT INTO #TABLE (firstname, lastname) VALUES ('Erik', 'Def')
INSERT INTO #TABLE (firstname, lastname) VALUES ('Harry', 'Xyz')
INSERT INTO #TABLE (firstname, lastname) VALUES ('Harry', 'Abc')
INSERT INTO #TABLE (firstname, lastname) VALUES ('Harry', 'Def')

SELECT * FROM #TABLE
ORDER BY firstname, lastname

DROP TABLE #TABLE

I hope this explains your question.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:18:22

why are you posting multiple threads for same issue
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169088

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

lasha90
Starting Member

5 Posts

Posted - 2011-12-13 : 08:56:53

thanx a lot kfluffie!! @visakh16..actually i am new to the forum and dint know the actual working..i thought query might be answered faster if posted here..anyways thanx a ton!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 09:00:04

quote:
Originally posted by lasha90

thanx a lot kfluffie!! @visakh16..actually i am new to the forum and dint know the actual working..i thought query might be answered faster if posted here..anyways thanx a ton!!

no problem
but in future please dont cross post

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources