SQL UNION with Replace in the ORDER BY clause

[tkltechy]

I have a sql query that unions two recordsets. It works when I don't try to adjust the ORDER BY clause using the replace function. However, I want to ensure that a certain item in the list always floats to the top. Can someone help me?

Below is the SQL that does not run. Error is "Invalid column name 'name':

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
replace([name],'PRIME','AAA')

Also tried this but got error "ORDER BY items must appear in the select list if the statement contains a UNION operator.":

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
replace(2,'PRIM E','AAA')


Below is the SQL that works:

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
[name]

[amitpatel66]

I have a sql query that unions two recordsets. It works when I don't try to adjust the ORDER BY clause using the replace function. However, I want to ensure that a certain item in the list always floats to the top. Can someone help me?

Below is the SQL that does not run. Error is "Invalid column name 'name':

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
replace([name],'PRIME','AAA')

Also tried this but got error "ORDER BY items must appear in the select list if the statement contains a UNION operator.":

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
replace(2,'PRIM E','AAA')


Below is the SQL that works:

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
[name]

Basically you are looking at 0 and PRIME to be at the TOP followed by the data is it??

Could you please provide some smaple data fetched by the second part of query in UNION?

[ck9663]

I have a sql query that unions two recordsets. It works when I don't try to adjust the ORDER BY clause using the replace function. However, I want to ensure that a certain item in the list always floats to the top. Can someone help me?

Below is the SQL that does not run. Error is "Invalid column name 'name':

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
replace([name],'PRIME','AAA')

Also tried this but got error "ORDER BY items must appear in the select list if the statement contains a UNION operator.":

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
replace(2,'PRIM E','AAA')


Below is the SQL that works:

Select 0 as [subcontractorID], 'PRIME' as [name]
UNION
Select
subcontractor.s ubcontractorID, subcontractor.[name]
from
subcontractor
order by
[name]

Sounds like you want this:

Select 0 as [subcontractorID], 'PRIME' as [name]

as your first record, right?

Try this:

Select 0 as [subcontractorID], 'PRIME' as [name], 0 as SortOrder
UNION
Select 	
subcontractor.subcontractorID, subcontractor.[name], 1 
from subcontractor
order by 3

You can use order by 3, 1 to order it by SubContractorId after the SortOrder. If you don't want the SortOrder to show up, place the entire query in a subquery and remove the SortOrder on your SELECT list. You might need to include TOP keyword since you have ORDER BY inside the subquery.

Happy Coding.

-- CK

[tkltechy]

Thanks a lot - this worked. I used "ORDER BY 3, 2" The 2 allowed the multiple data records in the second select to be ordered by the subcontractor.n ame while still getting the 'PRIME' to stay at the top of the list.

Thanks again!

Sounds like you want this:

Select 0 as [subcontractorID], 'PRIME' as [name]

as your first record, right?

Try this:

Select 0 as [subcontractorID], 'PRIME' as [name], 0 as SortOrder
UNION
Select 	
subcontractor.subcontractorID, subcontractor.[name], 1 
from subcontractor
order by 3

You can use order by 3, 1 to order it by SubContractorId after the SortOrder. If you don't want the SortOrder to show up, place the entire query in a subquery and remove the SortOrder on your SELECT list. You might need to include TOP keyword since you have ORDER BY inside the subquery.

Happy Coding.

-- CK

[jobspider1000]

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html