Order by - 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 2005 Forums

Transact-SQL (2005)

Order by

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 01:54:33

What does it mean order by anynumber means

SELECT * FROM table order by 3

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 01:57:22

it means order by 3rd column in your table. Each column in a table will have an ordinal number like 1,2,3... which you can get from below query

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION = 3 AND TABLE_NAME='Your table' AND TABLE_SCHEMA = 'your schema name'

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 02:07:07

<<
it means order by 3rd column in your table.
>>

Not necessarily. It is third column specified in the SELECT statement

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 02:10:44

quote:
Originally posted by madhivanan

<<
it means order by 3rd column in your table.
>>

Not necessarily. It is third column specified in the SELECT statement

Madhivanan

Failing to plan is Planning to fail

Yeah..thats also true
I stated that seeing his SELECT which involved only a single table

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 02:17:54

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

<<
it means order by 3rd column in your table.
>>

Not necessarily. It is third column specified in the SELECT statement

Madhivanan

Failing to plan is Planning to fail

Yeah..thats also true
I stated that seeing his SELECT which involved only a single table

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

Yes. My reply is more specific

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 02:19:56

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

<<
it means order by 3rd column in your table.
>>

Not necessarily. It is third column specified in the SELECT statement

Madhivanan

Failing to plan is Planning to fail

Yeah..thats also true
I stated that seeing his SELECT which involved only a single table

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

Yes. My reply is more specific

Madhivanan

Failing to plan is Planning to fail

more generic you meant I guess

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

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 02:21:41

Not considered it before, but I think this should be deprecated - and replaced with a more obvious syntax

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 02:26:03

Hey guys,

But if i am joining two or more table than for each table ordinal number 3 will be for different column then what ?

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 02:27:09

quote:
Originally posted by vaibhavktiwari83

Hey guys,

But if i am joining two or more table than for each table ordinal number 3 will be for different column then what ?

Vabhav T

then what Madhi said happens, it selects 3rd column that is specified in your select list and sorts based on it

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 02:30:00

Ok i got it...
anyways guys and girls (if any) thanks for replies

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 02:30:11

quote:
Originally posted by vaibhavktiwari83

Hey guys,

But if i am joining two or more table than for each table ordinal number 3 will be for different column then what ?

Vabhav T

You must read my first reply carefully

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 02:32:32

quote:
Originally posted by Kristen

Not considered it before, but I think this should be deprecated - and replaced with a more obvious syntax

No need. Note that it is supported in all RDBMSs and I think it may be part of ANSI

When you have too much dynamic sql, Order by number would be very helpful where you dont know the name of the column

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 02:51:27

" No need"

I think the need is demonstrated by this O/P.

ORDER BY 3

is not intuitive (ANSI or not!!). Looks like ordering by the constant-value "3" ...

I have used it for column-ordering, of course, when I was too lazy to retype the alias name for the column

P.S. By "Deprecated" I meant replace with a more obvious syntax such as

ORDER BY COLUMN_ORDINAL(3)

or somesuch

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 03:02:53

quote:
Originally posted by Kristen

" No need"

I think the need is demonstrated by this O/P.

ORDER BY 3

is not intuitive (ANSI or not!!). Looks like ordering by the constant-value "3" ...

I have used it for column-ordering, of course, when I was too lazy to retype the alias name for the column

P.S. By "Deprecated" I meant replace with a more obvious syntax such as

ORDER BY COLUMN_ORDINAL(3)

or somesuch

That certainly seems like a good suggestion as it adds more clarity

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 04:05:50

<<
ORDER BY COLUMN_ORDINAL(3)
>>

Then it is better you type the original column/alias name itself

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 04:30:55

Yes, that's a fair point. Are there circumstances where you don't (easily??) know the alias name (to use it in the ORDER BY clause)?

If not why have column-ordinal-number option for ORDER BY anyway??

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-20 : 05:25:04

Union and union all

SELECT name, object_id from sys.objects
union all
select 'All', 0
ORDER BY 2

Yes, it's obvious in this case, sometimes, especially if building up dynamic SQL, it's not.

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 05:46:57

quote:
Originally posted by Kristen

Yes, that's a fair point. Are there circumstances where you don't (easily??) know the alias name (to use it in the ORDER BY clause)?

If not why have column-ordinal-number option for ORDER BY anyway??

As I told you earlier in the post, when you use too much dynamic SQL, you may need it.

Consider you write a Dynamic PIVOT query where you pass Group by column, aggregate columns, etc and you want to Order by the group by column. Order by 1 will help you without knowing the column name itself

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 06:22:34

" Union and union all"

The Column Names / Alias from the first SELECT apply (is that right?)

I've always Aliased them in order to be able to have names in the ORDER BY

I don't quite get why Dynamic SQL could not have Alias names (how is the App going to provide a column name in the display?), but I concede that it is a useful short-hand

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 07:40:24

<<
I don't quite get why Dynamic SQL could not have Alias names
>>

Ok. Consider the Usage part of the artilce (http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx). You pass select statement in which the group by column is different in each case. What if you want it ordered by the first column. Instead of deriving from SELECT part, it is easy to use ORDER BY 1

<<
but I concede that it is a useful short-hand
>>

Mostly, Yes, in Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 09:01:23

quote:
Originally posted by madhivanan

<<
I don't quite get why Dynamic SQL could not have Alias names
>>

Ok. Consider the Usage part of the artilce (http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx). You pass select statement in which the group by column is different in each case. What if you want it ordered by the first column. Instead of deriving from SELECT part, it is easy to use ORDER BY 1

<<
but I concede that it is a useful short-hand
>>

Mostly, Yes, in Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

Yeah thats quite a good scenario where this comes handy

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources