TSQL - Union, Union All, Order By

I needed to do a pick list in an application that was a combined list from two different SQL tables. One table contains the fields ID, ProjectNo, and ProjectTitle. The other table contains the fields ID, TCode, and Description.

To begin with, for the pick-list to work I needed to have distinct values for the ID fields from the two different tables. I accomplished this by having the starting value for the ID field for one of the tables start at a value that I knew would always be higher than the other table.

The next thing that was needed was an SQL view to return the list. The list had to have the values for each table sorted but not mixed with each other. In other words my list had to be table A sorted plus table B sorted, not table A + B sorted.

What finaly gave me the results I needed was:

SELECT top 10000 ID, ProjectNo AS Value, ProjectTitle AS DescValue
FROM dbo.Project
order by Value
)Tab1
union all
select * from(
select top 10000 ID, TCode as Value, Description as DescValue
from Department
order by Value
)Tab2

There is quite a difference between doing a Union All vs. Union. Give it a try and see what you get.

I also tried these:

SELECT ID, ProjectNo AS Value, ProjectTitle AS DescValue
FROM dbo.Project
union 
select ID, TCode as Value, Description as DescValue
from Department
order by Value

SELECT ID, ProjectNo, ProjectTitle
FROM dbo.Project
group by ID, ProjectNo, ProjectTitle
union 
select ID, TCode, Description
from Department
group by ID, TCode, Description
order by 2 asc