Board index » delphi » using UNION and ORDER BY clauses

LionRed

Delphi Developer
Sat, 30 Apr 2005 10:24:18 GMT

using UNION and ORDER BY clauses

i am getting an error when i use the order by clause in a select statement
with a union

eg

select code,description,supplierno1 from stock where usesup='Y'
union
select code,description,supplierno2 from stock where usesup='N'
order by code

this gives me the following error

Invalid command
Invalid ORDER BY clause

all documentation states that the order by clause needs to be on the last
select statement, however i have tried all combinations of placing the order
by in different positions and it stil will not work

how can i use order by while also using the union clause??

any help would be greatly appreciated

thanks
cameron

 
John Kebe

Delphi Developer
Sat, 30 Apr 2005 22:29:38 GMT

Re:using UNION and ORDER BY clauses


Quote
"LionRed" <c.ha...@ihug.co.nz> wrote in message <news:UyZz9.6969$8o1.1155124@news.xtra.co.nz>...
> i am getting an error when i use the order by clause in a select statement
> with a union

> eg

> select code,description,supplierno1 from stock where usesup='Y'
> union
> select code,description,supplierno2 from stock where usesup='N'
> order by code

> this gives me the following error

> Invalid command
> Invalid ORDER BY clause

> all documentation states that the order by clause needs to be on the last
> select statement, however i have tried all combinations of placing the order
> by in different positions and it stil will not work

> how can i use order by while also using the union clause??

> any help would be greatly appreciated

> thanks
> cameron

I found this:
An ORDER BY clause on a UNION must use numerical offsets to refer to the columns.

select col_c, col_a, col_b from t1
union
select col_c, col_a, col_b  from t2
order by 1, 2;  -- will order by col_c, col_a

HTH,
John

LionRe

Delphi Developer
Mon, 02 May 2005 09:00:18 GMT

Re:using UNION and ORDER BY clauses


i have found the answer to this problem for anyone else interested.

You can use the ordinal value of the field to order it.  therefore in the
example i presented you would make the order by clause read as

order by 1

cameron

Quote
"LionRed" <c.ha...@ihug.co.nz> wrote in message

news:UyZz9.6969$8o1.1155124@news.xtra.co.nz...
Quote
> i am getting an error when i use the order by clause in a select statement
> with a union

> eg

> select code,description,supplierno1 from stock where usesup='Y'
> union
> select code,description,supplierno2 from stock where usesup='N'
> order by code

> this gives me the following error

> Invalid command
> Invalid ORDER BY clause

> all documentation states that the order by clause needs to be on the last
> select statement, however i have tried all combinations of placing the
order
> by in different positions and it stil will not work

> how can i use order by while also using the union clause??

> any help would be greatly appreciated

> thanks
> cameron

Other Threads

1. Can you someway edit Query result set which used order by clause

2. Parameterised queries and UNION clause

3. Memo fields in select clause of union queries

4. Interbase equivalent to UNION clause

5. The clause UNION

6. UNION clause in SQL select

7. SQL Union clause (Local SQL)

8. Union clause with paradox tables

9. Possible BUG in LOCAL SQL UNION clause

10. SQL: UNION in SELECT clause