how can you pre-sort in a UNION

Hello:
I would like to not use a temporary table but I cannot seem to find a
solution on how to return
a recordset as the union of two recordsets with the second recordset sorted.
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
UNION
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
FROM DOCTYPE
RETURN
-- I want A"NY TYPE" to always be the first row and the other rows to be
sorted by vcDOCTYPEexplicit
-- I tried using a sorted view for the second SELECT statement but the union
unfortunately destroys the sort order: a priori, I dont see why
it does that as it would be a great workaround...
Thanks
TerrySupport
SELECT <column lists> FROM
(
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
UNION ALL
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
FROM DOCTYPE
) AS Der ORDER BY --what you want
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:umW3r%239pFHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset
> sorted.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the
> union unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
> Thanks
> Terry
>|||Try this
Select * From(
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
UNION
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
FROM DOCTYPE
) T order by case when vcDOCTYPEexplicit='Any type' then 1 else 0 end
Madhivanan|||Select * from
(
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
1 as SortOrder
UNION
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
2 as SortOrder
FROM DOCTYPE
)
Order by SortOrder,vcDOCTYPEexplicit
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Support" wrote:

> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset sorte
d.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the uni
on
> unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
> Thanks
> Terry
>
>|||Support wrote:
> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset sorte
d.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the uni
on
> unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
Why it doesn't work the second way: A union says "return me a unique
set of rows", so after all rows are produced by the individual selects,
they're all sorted into an order (what order is determined by the
system) and eliminates the duplicates, then returns them. You might
then think "If I do a UNION ALL" (where it doesn't remove duplicates),
that'll work. And for some queries it may, but that is by no means
guaranteed, since a UNION ALL tells the system that it can return rows
from any of the selects as soon as those rows are available.
Best I can think of is to have the top select return a 0 in 1 column
(e.g. Called SortOrder) followed by any values you want/need for
remaining columns, and have the bottom select return 1 followed by the
columns on which sorting is done. Then you can order the whole union on
SortOrder, <other columns>
Damien|||Thanks to all: good help!
Take care
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:umW3r%239pFHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset
> sorted.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the
> union unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
> Thanks
> Terry
>