order by Family_Name or First_Name

[dotJoon]

myTable1
ID1   Family_Name   Family_Ace

1      Tailor            0 
2      Fonda             0
3      Jackson           1

myTable2

 ID2  Table1_ID    First_Name        elder  First_Ace  

1       1             Robert          1       0 
2       1             Liz             0       1 
3       3             Michael         1       0 
4       2             Jane            0       1 
5       2             Henry           0       0 
6       3             Tom             0       0

I have the above data in myTable1 and myTable2.
And I have the below CFML code.

CFML Code
<cfquery datasource="DS" name="test">
	select Family_Name, First_Name, Family_Ace, First_Ace
	from myTable1, myTable2
	where ( ID1=Table1_ID and Family_Ace=1 and elder=1) or 
                      ( ID1=Table1_ID and First_Ace=1)		
	order by ID1 
</cfquery>


<cfoutput query="test">
	<cfif Family_Ace is 1>	
		#Family_Name#
	<cfelseif First_Ace is 1>
		#First_Name#
	</cfif><br>	
</cfoutput>

The CFML code will produce the following result.

result

Liz 
Jane 
Jackson

I like to produce the result in Alphabetical order.
My Target result would be like the following.

Target Result

Jackson
Jane
Liz

Thanks in Advance

[r937]

<cfquery datasource="DS" name="test">
select Family_Name
     , First_Name
     , Family_Ace
     , First_Ace
     , case when Family_Ace=1
            then Family_Name
            else First_Name
        end as Display_Name
  from myTable1
inner
  join myTable2
    on ID1 = Table1_ID 
 where Family_Ace=1 and elder=1
    or First_Ace=1
order 
    by Display_Name 
</cfquery>


<cfoutput query="test">
 #Display_Name#<br />
</cfoutput>

[dotJoon]

Thanks, it works.