Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Order of records if no option provided

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bharti P Manglani

New User


Joined: 13 Mar 2007
Posts: 16
Location: Pune

PostPosted: Thu Aug 09, 2007 2:23 pm    Post subject: Order of records if no option provided
Reply with quote

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. icon_sad.gif
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 09, 2007 2:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 09, 2007 2:55 pm    Post subject:
Reply with quote

by the way, ORDER BY in not an option. The proper term is clause
Back to top
View user's profile Send private message
Bharti P Manglani

New User


Joined: 13 Mar 2007
Posts: 16
Location: Pune

PostPosted: Thu Aug 09, 2007 3:11 pm    Post subject: Re: Order of records if no option provided
Reply with quote

Hi,

Thanks a lot!! And yes, order by is a clause not an option. Thanks again for that!! icon_biggrin.gif
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 Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us