Page 1 of 1
ORDER BY and the columns requirement in DB2.
Posted: Mon Sep 14, 2015 1:55 pm
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?
Re: ORDER BY and the columns requirement in DB2.
Posted: Mon Sep 14, 2015 3:30 pm
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?
Re: ORDER BY and the columns requirement in DB2.
Posted: Wed Sep 16, 2015 9:12 pm
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.
Re: ORDER BY and the columns requirement in DB2.
Posted: Tue Apr 05, 2016 2:12 pm
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.
Re: ORDER BY and the columns requirement in DB2.
Posted: Wed Apr 06, 2016 11:19 am
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
Re: ORDER BY and the columns requirement in DB2.
Posted: Wed Apr 06, 2016 2:23 pm
by enrico-sorichetti
wouldn' t have been faster to try it ?
Re: ORDER BY and the columns requirement in DB2.
Posted: Fri May 06, 2016 12:14 pm
by Manju Mainframe
Thanks, tried it and got the result that column names should be identified and not necessarily be part of the select.