Incorrect Syntax with UNION 1

[auchtum]

Hello, I'm trying to create a simple UNION but is not working is displaying the next message:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Incorrect syntax near the keyword 'UNION'.

------------------------------
ADDITIONAL INFORMATION:

Incorrect syntax near the keyword 'UNION'. (Microsoft SQL Server, Error: 156)

For help, click:

http://go.microsoft.com/fwlink?Prod...406&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

And this is my UNION:

SELECT IdUsuario, Nombre
FROM Usuario
ORDER BY Nombre
UNION
SELECT Null, 'All'

How can I solve this?

 

[r937]

remove the ORDER BY clause

r937.com | rudy.ca

 

[auchtum]

Thank you so much.

 

[bborissov]

Depends what you want.
Where record should be (The last one used in UNION)?
The first record in the recordset or the last one?
I would not leave this on SQL Server wishes

SELECT IdUsuario, Nombre
FROM (SELECT IdUsuario, Nombre, 1 AS OrderBy
              FROM Usuario
      UNION
      SELECT Null, 'All', 2 AS OrderBy) Tbl1
ORDER BY OrderBy, Nombre

That is if you want "ALL" record to be the last, if you want it to be the first:

SELECT IdUsuario, Nombre
FROM (SELECT IdUsuario, Nombre, 1 AS OrderBy
              FROM Usuario
      UNION
      SELECT Null, 'All', 2 AS OrderBy) Tbl1
ORDER BY OrderBy DESC, Nombre

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.