Order By in Subquery + UNION

All,

I've seen several posts regarding using UNION or UNION ALL to mash
together two or more resultsets into a single result set, but can't
seem to find enough info here to help me answer my particular
question.

I have a stored procedure that gets the column names in a particular
format (i.e. "chassis_id"|"chassis_description"|"modify_date") as well
as actual data for a given table (in a quote-separated, pipe-delimited
manner i.e. "1"|"description for the chassis"|"2004-09-08").

I'd like to get both of these resultsets and mash them together. This
works, but when I need to order the second resultset (i.e. select *
from chassis order by chassis_id), SQL Server returns an error
complaining about the chassis_id column name (invalid column name
'chassis_id') in the Order By clause.

From what I can tell, I'm using the UNION and Order By in correctly,
but I'm not sure exactly what's wrong with it. If I take out the Order
By, everything works great. Although I would like to be able to order
my second resultset (in the same sproc) if possible.

The actual queries I'm running are actually quite long, but here's one
that's a bit shorter to help illustrate:

SELECT '"app_group_id"|"app_group_name"|"create_date"|"create_by"|"modify_date"|"modify_by"'
UNION ALL
SELECT + ISNULL(CONVERT(varchar,app_group_id), '') + '|'
+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 1))), 1,0)
+ '"' + ISNULL(CONVERT(varchar(1000), +
REPLACE(CONVERT(nvarchar(4000),app_group_name), '"', '""')), '') +
'"|'
+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 2))), 1,0)
FROM app_group
order by app_group_id

Thank for any help on this.

/bcIn a UNION any columns in the ORDER BY list must appear in the result. If
you don't want to include the ORDER BY column in the result then nest the
UNION as a derived table:

SELECT x
FROM
(SELECT NULL /* app_group_id column */ ,
... etc
UNION ALL
SELECT app_group_id,
... etc
FROM app_group) AS T(app_group_id,x)
ORDER BY app_group_id

--
David Portas
SQL Server MVP
--|||CREATE A VIEW WITHOUT THE ORDER BY CLAUSE. THEN SELECT COLUMNS FROM
VIEW USING THE ORDER BY CLAUSE. IF I REMEMBER RIGHT, UNION DOES NOT
LIKE ORDER BY.

Thanks, Girish

blake@.caraways.net (Blake Caraway) wrote in message news:<9089d10f.0409080819.5fb0ca55@.posting.google.com>...
> All,
> I've seen several posts regarding using UNION or UNION ALL to mash
> together two or more resultsets into a single result set, but can't
> seem to find enough info here to help me answer my particular
> question.
> I have a stored procedure that gets the column names in a particular
> format (i.e. "chassis_id"|"chassis_description"|"modify_date") as well
> as actual data for a given table (in a quote-separated, pipe-delimited
> manner i.e. "1"|"description for the chassis"|"2004-09-08").
> I'd like to get both of these resultsets and mash them together. This
> works, but when I need to order the second resultset (i.e. select *
> from chassis order by chassis_id), SQL Server returns an error
> complaining about the chassis_id column name (invalid column name
> 'chassis_id') in the Order By clause.
> From what I can tell, I'm using the UNION and Order By in correctly,
> but I'm not sure exactly what's wrong with it. If I take out the Order
> By, everything works great. Although I would like to be able to order
> my second resultset (in the same sproc) if possible.
> The actual queries I'm running are actually quite long, but here's one
> that's a bit shorter to help illustrate:
> SELECT '"app_group_id"|"app_group_name"|"create_date"|"create_by"|"modify_date"|"modify_by"'
> UNION ALL
> SELECT + ISNULL(CONVERT(varchar,app_group_id), '') + '|'
> + SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 1))), 1,0)
> + '"' + ISNULL(CONVERT(varchar(1000), +
> REPLACE(CONVERT(nvarchar(4000),app_group_name), '"', '""')), '') +
> '"|'
> + SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 2))), 1,0)
> FROM app_group
> order by app_group_id
>
> Thank for any help on this.
> /bc|||Girish (kattukuyil@.hotmail.com) writes:
> CREATE A VIEW WITHOUT THE ORDER BY CLAUSE. THEN SELECT COLUMNS FROM
> VIEW USING THE ORDER BY CLAUSE. IF I REMEMBER RIGHT, UNION DOES NOT
> LIKE ORDER BY.

You remember wrong. But it is imporant to understand that ORDER BY
applies to the entire query, not the individual SELECT clauses in the
query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp