RE: sql help

Another option might be this.  I don't know how it would compare performance 
wise to the union solution.

select contract, flag, case master when ' ' then contract else master as master
order by 3, 2 desc

Rick

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Smith, Mike
> Sent: Monday, January 26, 2004 2: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