ORDER BY and the columns requirement in DB2.

RDBMS from IBM and IBM's Hierarchical DBMS and Transaction Manager (IMS DC).
Post Reply
Manju Mainframe
Registered Member
Posts: 24
Joined: Mon Aug 12, 2013 10:50 am

ORDER BY and the columns requirement in DB2.

Post by Manju Mainframe »

Hi,

In "Order By" Clause in DB2, does the clause require the columns to be selected using select statement on which order is to be performed? If it is not then how DB2 knows about those columns?
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: ORDER BY and the columns requirement in DB2.

Post by nicc »

Have you read up about the ORDER BY clause in the DB2 manual? How do you expect something to be sorted if you have not selected anything to be sorted by?
Regards
Nic
zprogrammer
Global Moderator
Global Moderator
Posts: 588
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars

Re: ORDER BY and the columns requirement in DB2.

Post by zprogrammer »

Quoting from manual
Column names in sort keys: A column name in a sort-key must conform to the following rules:

If the column name is qualified, the query must be a subselect. The column name must unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect; its
value is used to compute the value of the sort specification.

If the column name is unqualified and the query is a subselect, the column name must unambiguously identify a column of the result table. In this case, column-name must bt the name of a named column in
the select list. If the column name is identical to one column of the result table, its value is used to compute the value of the sort specification. If the column name is not found in the result table, it must
unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect.
zprogrammer
Manju Mainframe
Registered Member
Posts: 24
Joined: Mon Aug 12, 2013 10:50 am

Re: ORDER BY and the columns requirement in DB2.

Post by Manju Mainframe »

Pandora-Box wrote:Quoting from manual
Column names in sort keys: A column name in a sort-key must conform to the following rules:

  If the column name is qualified, the query must be a subselect.  The column name must unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect; its
   value is used to compute the value of the sort specification.

  If the column name is unqualified and the query is a subselect, the column name must unambiguously identify a column of the result table. In this case, column-name must bt the name of a named column in
   the select list.  If the column name is identical to one column of the result table, its value is used to compute the value of the sort specification. If the column name is not found in the result table, it must
   unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect.
Thank you! We need to the column names when we use ORDER BY is the conclusion.
Chandan Yadav
Website Team
Website Team
Posts: 70
Joined: Wed Jul 31, 2013 10:19 pm

Re: ORDER BY and the columns requirement in DB2.

Post by Chandan Yadav »

Hi,

@ Manju are you saying We need to the select column names when we use ORDER BY is the conclusion.?

If yes then its not the case always. For first case its not required to be a part of result table but it should be identified as the column of table

Try to execute the queries with having order by column different than columns in Select clause

Thanks,
Chandan
enrico-sorichetti
Global Moderator
Global Moderator
Posts: 825
Joined: Wed Sep 11, 2013 3:57 pm

Re: ORDER BY and the columns requirement in DB2.

Post by enrico-sorichetti »

wouldn' t have been faster to try it ?  8-)
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort 8-)
Manju Mainframe
Registered Member
Posts: 24
Joined: Mon Aug 12, 2013 10:50 am

Re: ORDER BY and the columns requirement in DB2.

Post by Manju Mainframe »

Thanks, tried it and got the result that column names should be identified and not necessarily be part of the select.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DB2 and IMS DB/DC”