Order By in Subquery + UNION

[Blake Caraway]

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_dat e") 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"|"cr eate_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

[David Portas]

In 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
--

[Girish]

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
bl***@caraways.net (Blake Caraway) wrote in message news:<90**************************@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_dat e") 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"|"cr eate_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

[Erland Sommarskog]

Girish (ka********@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, es****@sommarskog.se

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