Using UNION and ORDER BY clauses 
Author Message
 Using UNION and ORDER BY clauses

I've got a little problem that has me stumped (and I expect it's a real easy
solution, it's just escaping me at the moment).

I currently have a long SELECT statement to retrieve data (within a Stored
Procedure). Recently, I decided that I wanted to add more data to the Stored
Procedure. So my solution was to open up the Stored Procedure, and cut &
paste the entire SELECT statement, copying it verbatim, and separated by the
UNION ALL operator.

But when I try it, I get the error: "Error 156: Incorrect syntax near the
keyword 'UNION'."

Now, both select statements currently end with an ORDER BY clause, and I
still want to return the data in the order as specified by that clause
(except for all the data rather than just one SELECT or the other). I
discovered if I comment out the ORDER BY clauses, it accepts the syntax.

Is there a way I can do this UNION (hopefully easily) without losing the
ORDER BY?

Thanks!
-Scott



Sun, 10 Oct 2004 00:43:12 GMT
 Using UNION and ORDER BY clauses

There can only be one ORDER BY clause. It should be after
all the SELECT statements.

Quote:
>-----Original Message-----
>I've got a little problem that has me stumped (and I

expect it's a real easy
Quote:
>solution, it's just escaping me at the moment).

>I currently have a long SELECT statement to retrieve

data (within a Stored
Quote:
>Procedure). Recently, I decided that I wanted to add

more data to the Stored
Quote:
>Procedure. So my solution was to open up the Stored

Procedure, and cut &
Quote:
>paste the entire SELECT statement, copying it verbatim,

and separated by the
Quote:
>UNION ALL operator.

>But when I try it, I get the error: "Error 156:

Incorrect syntax near the
Quote:
>keyword 'UNION'."

>Now, both select statements currently end with an ORDER
BY clause, and I
>still want to return the data in the order as specified
by that clause
>(except for all the data rather than just one SELECT or
the other). I
>discovered if I comment out the ORDER BY clauses, it
accepts the syntax.

>Is there a way I can do this UNION (hopefully easily)
without losing the
>ORDER BY?

>Thanks!
>-Scott

>.



Sun, 10 Oct 2004 00:52:58 GMT
 Using UNION and ORDER BY clauses
You have the answer in your question itself. While doing UNION you can have
only one ORDER BY in the last query.

The only other option which I can think of, if you want to preserve the
first ORDER
by is to insert the first query with the ORDER BY into a #temp table. Then,
insert
the second Query Results into it and then SELECT from the #temp table

Thanks
Anith


Quote:
> I've got a little problem that has me stumped (and I expect it's a real
easy
> solution, it's just escaping me at the moment).

> I currently have a long SELECT statement to retrieve data (within a Stored
> Procedure). Recently, I decided that I wanted to add more data to the
Stored
> Procedure. So my solution was to open up the Stored Procedure, and cut &
> paste the entire SELECT statement, copying it verbatim, and separated by
the
> UNION ALL operator.

> But when I try it, I get the error: "Error 156: Incorrect syntax near the
> keyword 'UNION'."

> Now, both select statements currently end with an ORDER BY clause, and I
> still want to return the data in the order as specified by that clause
> (except for all the data rather than just one SELECT or the other). I
> discovered if I comment out the ORDER BY clauses, it accepts the syntax.

> Is there a way I can do this UNION (hopefully easily) without losing the
> ORDER BY?

> Thanks!
> -Scott



Sun, 10 Oct 2004 00:54:50 GMT
 Using UNION and ORDER BY clauses
order by affects the entire statement. this is well documented in sql book
online under select/union/orderby.

how about this...

select a,b,c,d
from (select '0' as ord, a, b,c,d
from tb1
union all
select '1',a,b,c,d
from tb2)x
order by ord

--
-oj

http://www.rac4sql.com


Quote:
> I've got a little problem that has me stumped (and I expect it's a real
easy
> solution, it's just escaping me at the moment).

> I currently have a long SELECT statement to retrieve data (within a Stored
> Procedure). Recently, I decided that I wanted to add more data to the
Stored
> Procedure. So my solution was to open up the Stored Procedure, and cut &
> paste the entire SELECT statement, copying it verbatim, and separated by
the
> UNION ALL operator.

> But when I try it, I get the error: "Error 156: Incorrect syntax near the
> keyword 'UNION'."

> Now, both select statements currently end with an ORDER BY clause, and I
> still want to return the data in the order as specified by that clause
> (except for all the data rather than just one SELECT or the other). I
> discovered if I comment out the ORDER BY clauses, it accepts the syntax.

> Is there a way I can do this UNION (hopefully easily) without losing the
> ORDER BY?

> Thanks!
> -Scott



Sun, 10 Oct 2004 01:00:59 GMT
 Using UNION and ORDER BY clauses
Scott,

I'll suppose what you have now is

select Col1, Col2, Col3
from T
order by Col3, Col2
union all
select ColA, ColB, ColC
order by ColA, ColB

  You can then get the ordering you want this way:

select Col1, Col2, Col3 from (
  select 1 as whichPart, Col1, Col2, Col3,
  Col3 as OrderCol1_1, Col2 as OrderCol1_2,
  null as OrderCol2_1, null as OrderCol2_2
  <rest of first select>
  union all
  select 2, ColA, ColB, ColC, null, null, ColA, ColB
  <rest of second select>
) T
order by
  whichPart,
  OrderCol1_1, OrderCol1_2,
  OrderCol2_1, OrderCol2_2

Steve Kass
Drew University

Quote:

> I've got a little problem that has me stumped (and I expect it's a real easy
> solution, it's just escaping me at the moment).

> I currently have a long SELECT statement to retrieve data (within a Stored
> Procedure). Recently, I decided that I wanted to add more data to the Stored
> Procedure. So my solution was to open up the Stored Procedure, and cut &
> paste the entire SELECT statement, copying it verbatim, and separated by the
> UNION ALL operator.

> But when I try it, I get the error: "Error 156: Incorrect syntax near the
> keyword 'UNION'."

> Now, both select statements currently end with an ORDER BY clause, and I
> still want to return the data in the order as specified by that clause
> (except for all the data rather than just one SELECT or the other). I
> discovered if I comment out the ORDER BY clauses, it accepts the syntax.

> Is there a way I can do this UNION (hopefully easily) without losing the
> ORDER BY?

> Thanks!
> -Scott



Sun, 10 Oct 2004 01:07:04 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Order By clause with UNION : SQL Server 2000

2. Help : Order by with Union clause

3. UNION and ORDER BY clause

4. order by clause not working with union

5. Using Union subquery in select clause

6. SQL question: using UNION in FROM clause with DB2 V5

7. Using order by Upper in union statement

8. Using ORDER BY in UNION SQL Statement

9. using union and order by...

10. Retaining sort orders of the select queries appened togather using UNION

11. Using ORDER BY in a UNION

12. Using order by with union


 
Powered by phpBB® Forum Software