|
View previous topic :: View next topic
|
| Author |
Message |
Bharti P Manglani
New User
Joined: 13 Mar 2007
Posts: 16
Location: Pune
|
|
|
|
Hi All,
I have a question:
Given the following table definition:
SALES
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER
If the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced?
SELECT * FROM sales:
A. The rows are sorted by INVOICE_NO in ascending order.
B. The rows are sorted by INVOICE_NO in descending order.
C. The rows are ordered based on when they were inserted into the table.
D. The rows are not sorted in any particular order.
I think the option should be C. But the answer provided in the book is D.
Please help.  |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Craig Mullins DB2 Dev Guide wrote: |
You cannot guarantee the order of the rows returned from a SELECT statement without an ORDER BY clause. At times SQL developers get confused when DB2 uses an index to satisfy a query and the results are returned in the desired order even without the ORDER BY clause. But, due to the nature of the DB2 optimizer, the access path by which the data is retrieved might change from execution to execution of an application program. If the access path changes, and ORDER BY is not specified, the results can be returned in a different (non-desired) order. For this reason, always code the ORDER BY clause when the sequence of rows being returned is important. |
If you are basing your thinking on knowledge that VSAM inserts do not reorder the base but only the index, you are forgetting about REORG. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| by the way, ORDER BY in not an option. The proper term is clause |
|
| Back to top |
|
|
Bharti P Manglani
New User
Joined: 13 Mar 2007
Posts: 16
Location: Pune
|
|
|
|
Hi,
Thanks a lot!! And yes, order by is a clause not an option. Thanks again for that!!  |
|
| Back to top |
|
|
|
|
