IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 SQL doubt - Sorting by non group by field


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
himanshupant

New User


Joined: 21 Mar 2007
Posts: 46
Location: India

PostPosted: Fri Mar 19, 2010 8:29 am
Reply with quote

Hi

Below is the DCLGEN of a table :-

Code:
PAR                        SMALLINT NOT NULL,     
BR                          INTEGER NOT NULL,       
ACC                        INTEGER NOT NULL,       
RUN_DATE               DATE NOT NULL,         
A_U_IND                 CHAR(1) NOT NULL,       
DRATE                    CHAR(4) NOT NULL,       
START_DA              DATE NOT NULL,         
ITYPE                     CHAR(1) NOT NULL,       
BLIMIT                   DECIMAL(13, 2) NOT NULL,
THOLD                   DECIMAL(15, 0) NOT NULL,
AIT                        DECIMAL(15, 0) NOT NULL

In the above table the keys are the column 1 - 6 ( i.e. from PAR till DRATE both inclusive).

Below is a query to return some rows from the table

Code:
   SELECT                                             
               A_U_IND                         
               , MIN(RUN_DATE)                             
               , DRATE                                 
               , THOLD                         
         FROM                                               
               TABLE1                           
         WHERE                                               
             PAR = :TABLE1.PAR
   AND       BR = :TABLE1.BR
   AND       ACC = :TABLE1.ACC               
   AND       RUN_DATE BETWEEN :WS-START-DATE AND :WS-END-DATE
   AND       ITYPE IN ('A' 'B' 'T')           
   GROUP BY A_U_IND,DRATE,THOLD             
     ORDER BY THOLD ASC   


In the above query I want to keep everything unchanged but just ensure that the results come in the sorted ascending order of RUN_DATE as well ( in addition to THOLD) . I am trying to put in ORDER by RUN_DATE as well but I am getting a SQL code of -122 . Is there any way this query can be redisgned to provide the same result set with the additional condition of being sorted by RUN_DATE as well.

Any help would be greatly appreciated.


2) Can any one tell me what is the SORT criterial in the above query? I see a trend of the result set being sorted on the basisi of A_U_IND ( when the THOLD are same). Is DB2 engine doing some other sort criteria on its own ? Can we play around with that SORT criteria to get our desired criteria of SORT on basis of RUN_DATE.?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 19, 2010 4:53 pm
Reply with quote

This is basic sql knowledge:
order by thold,min(run_date)
or
order by 4,2

If you don't specify order by or only one column (thold) the order of the rows is undefined and depends on the accesspath which can change on any rebind.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts How to move the first field of each r... DFSORT/ICETOOL 5
No new posts Compare latest 2 rows of a table usin... DB2 1
Search our Forums:

Back to Top