RE: sql help

As long as you don't mind an extra field....

select contract, flag, master,
         case
        when flag = 'y' then contract
            when flag = 'n' then master
         end as group_key
from myfile
order by group_key

HTH,
Charles


> -----Original Message-----
> From: Smith, Mike [mailto:Mike_Smith@xxxxxxxxxxxxxxxx]
> Sent: Monday, January 26, 2004 3:08 PM
> To: Midrange Systems Technical Discussion
> Subject: sql help
> 
> 
> I have a file that has  a contract#, a flag that indicates if the file
> is a master contract, and a master contract#
> so i might have something like 
>  
> contract    flag    master
> 1                y
> 2                y
> 3                n        1
> 4                n        2
> 5                n        1
>  
>  
> where contract 1 is a master contract with subordinate 
> contracts 3 and 5
>  
> i'm trying to create an SQL statement that will list the master
> contracts and the subordinate contracts. So far i haven't been able to
> get this to work.  Can i do this in 1 statement. 
>  
> i want results such as 
>  
>  
> contract    flag    master
> 1                y
> 3                n        1
> 5                n        1
> 2                y        
> 4                n        2
> _______________________________________________
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>