Jerry
you don't need to used 3 queries to do this

try this 
/*******************************************
Select Len(Route_Name) As DummyCol1,Case
	when left(Country,3)='Amer'then 'Amer'
	end AS DummyCol2,* from Orders
where Country like '%Amer%'
order by DummyCol1,DummyCol2 desc
********************************************/
substitute your column name for 'Country'
and your table for 'Orders'

The first dummy column can not be less then len('Amer'),your first 
criteria.
The second dummy column captures Amer and Amer% and makes other rows null

The 'order by DummyCol1,DummyCol2 desc' clause uses the first column  in 
asscending order and the second col in descending order to force null to 
the bottom.

It would be better to have an index on the column being searched.
With the above example (and no unions) there is only one pass at the data 
or index.


Regards

Greg Frazer

> I have three queries, all with the same structure, that I'm using in a =
search routine of a company table.  The only way the three vary is with =
the where clause.  Query1 looks for an exact match to the criteria =
entered (i.e. =3D 'Amer').  Query2 looks for companies starting with the =
search terms entered (i.e. Like 'Amer%').  Query3 looks for companies =
containing the search terms (i.e. Like '%Amer%').

I'm using the Union operator to dump these results into one recordset.  =
I choose to use the union because it pulls out the duplicates for me, =
since anything that is returned by Query1 will also be returned in =
Query2 and Query3.  Likewise, anything returned in Query2 will also be =
returned by Query3.

My problem stems from the order SQL Server gives me the result set back, =
ordered by Company_Key.  What I want is for the results of Query1 to be =
at the top of the result set followed by Query2 and Query3 =
consecutively.   Does anyone know how I could do this.

Right now, I can see myself running three seperate queries, without the =
Union.  Then dumping all three queries into seperate arrays and writing =
a function that combines the three arrays into a new array in the order =
I want, without duplicates.  However, I don't cherish the idea of =
writing that function, so if anyone can tell me how to get the records =
in the order I want, I would appreciate it.


Thanks

Jerry