Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Difference between order by column name and order by(nos

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Parasakthi
Currently Banned

New User


Joined: 14 Feb 2008
Posts: 1
Location: Bangalore

PostPosted: Fri Feb 22, 2008 10:49 am    Post subject: Difference between order by column name and order by(nos
Reply with quote

Hi all,

Where we have to use order by 1,2 clause?

What is the difference between order by column name and order by(nos)..
Back to top
View user's profile Send private message

vasanthkumarhb

Active User


Joined: 06 Sep 2007
Posts: 276
Location: Bang,iflex

PostPosted: Fri Feb 22, 2008 1:15 pm    Post subject:
Reply with quote

Hi......

GROUP BY class concentrates on fetching data row wise, where as ORDER BY specifies fetching the data in column wise.

consider one example query for analysis.

SELECT DEPT,COUNT(*) FROM STAFF GROUP BY DEPT HAVING MAX(COMM)>5000 ORDER BY 2 DESC

Here query considering on the 2nd column that is DEPT and the maximum of commission and grouped together after satisfying HAVING condition.

If u want to operate on the particular column or particular group u can give ORDER BY 2(2nd column) DESC or GROUP BY 3(3rd row) ASC(default)
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Feb 22, 2008 6:42 pm    Post subject:
Reply with quote

Did you even read the O/P? They didn't ask anything about GROUP BY.

Parasakthi: The syntax of ORDER BY allows you to refer to columns either by name or by position. Both are valid references and give them same end result.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Fri Feb 22, 2008 6:57 pm    Post subject:
Reply with quote

But of course the danger in using numbers is that they must be adjusted if someone later modifies the SELECT list, and names are more meangful.

However, if you have a bunch of UNIONs, the col names most likely differ, so I think you must use numbers.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Fri Feb 22, 2008 7:24 pm    Post subject:
Reply with quote

but sometimes you need to use NUMBERS becouse the field you need to order by is not a column but a function, for example, column3+column4-column5...
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Fri Feb 22, 2008 7:50 pm    Post subject:
Reply with quote

Can't you give a name to a SELECT expression?
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Fri Feb 22, 2008 8:52 pm    Post subject:
Reply with quote

I cannot test it now BUT I think you cannot use that name in order by... can anyone try it?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us