Add order by to Union Select ALL

Question

I am using the following query to create an ssrs parameter and need to order by lastname

SELECT  Lastname + ', ' + FirstName + ' ' + '(' + INPN + ')' as Agent

FROM tblagents where agentcode in (SELECT AgentCode from AgentDetail)

UNION
SELECT ' ALL'  AS Agent 

I attempted first to use order by prior to the union.

Then i tried to wrap this all in a select * from

SELECT  *
from (
    (SELECT Lastname, Firstname, INPN FROM tblAgents where agentcode in (SELECT AgentCode from AgentDetail) )
    UNION SELECT ' ALL' AS Agent
) 

that did not work either. How can i order by last name in my query?

Answer 1

The sintaxis is order by at the end. Not before the union

SELECT * FROM Table1
UNION 
SELECT * FROM Table2
ORDER BY <somefield>

In your case something like this:

SELECT Lastname as OrderField, 
       Lastname + ', ' + FirstName + ' ' + '(' + INPN + ')' as Agent

FROM tblagents 
WHERE agentcode in (SELECT AgentCode FROM AgentDetail)

UNION ALL 

SELECT 'AAAAA' as OrderField,
        'ALL'  AS Agent 
ORDER BY OrderField

Answer 2

SELECT T.Lastname, T.Firstname, T.INPN , T.Agent 
FROM (
        SELECT Lastname, Firstname, INPN , null as Agent
        FROM tblAgents 
        WEHRE agentcode in (SELECT AgentCode 
                            from AgentDetail)
        UNION 
        SELECT null,null,null, 'ALL' AS Agent
      ) AS T
ORDER BY T.Lastname asc