Sorting With a UNION Query?

[MDW]

Hey all,

I'm working with SQL server 2000 (upsized from MS Access 2000) and I've been able to reconcile all my SQL differences but one. I build a fairly complex recordset that consists of a UNION from two different SELECT statements. It ends up looking like this:

SELECT
Col1,
Col2,
Col3
FROM
Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC
UNION SELECT
Col2
Col5
'N/A'
FROM
Table2 WHERE Col5='bbb'

When my ASP was hooked into Access, that statement executed fine. However, SQL server returns an error that says "Invalid syntax near keyword UNION". I've tried surrounding the SELECT statements in parenthasees (SELECT ... ORDER BY ...) UNION (SELECT ...), but that didn't work. Access was happy with it either way.

Any suggestions as to what I could be doing wrong?

[Chris Hohmann]

"MDW" <MDW@discussions.microsoft.com> wrote in message
news:D5B5EE74-8269-46E2-8A56-D79F60705EB9@microsoft.com...

> Hey all,
>
> I'm working with SQL server 2000 (upsized from MS Access 2000) and I've

been able to reconcile all my SQL differences but one. I build a fairly
complex recordset that consists of a UNION from two different SELECT
statements. It ends up looking like this:

>
> SELECT
> Col1,
> Col2,
> Col3
> FROM
> Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC
> UNION SELECT
> Col2
> Col5
> 'N/A'
> FROM
> Table2 WHERE Col5='bbb'
>
> When my ASP was hooked into Access, that statement executed fine.

However, SQL server returns an error that says "Invalid syntax near keyword
UNION". I've tried surrounding the SELECT statements in parenthasees (SELECT
.... ORDER BY ...) UNION (SELECT ...), but that didn't work. Access was
happy with it either way.

>
> Any suggestions as to what I could be doing wrong?

Put the ORDER BY clause at the end. See example (f) under the "SELECT
Examples" topic in BOL. Note, that's a lowercase "f".

[Aaron [SQL Server MVP]]

You can only sort the result as a whole, not the individual queries.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"MDW" <MDW@discussions.microsoft.com> wrote in message
news:D5B5EE74-8269-46E2-8A56-D79F60705EB9@microsoft.com...

> Hey all,
>
> I'm working with SQL server 2000 (upsized from MS Access 2000) and I've

been able to reconcile all my SQL differences but one. I build a fairly
complex recordset that consists of a UNION from two different SELECT
statements. It ends up looking like this:

>
> SELECT
> Col1,
> Col2,
> Col3
> FROM
> Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC
> UNION SELECT
> Col2
> Col5
> 'N/A'
> FROM
> Table2 WHERE Col5='bbb'
>
> When my ASP was hooked into Access, that statement executed fine.

However, SQL server returns an error that says "Invalid syntax near keyword
UNION". I've tried surrounding the SELECT statements in parenthasees (SELECT
.... ORDER BY ...) UNION (SELECT ...), but that didn't work. Access was
happy with it either way.

>
> Any suggestions as to what I could be doing wrong?