ORDER BY and UNION ALL

2 things:
* DON'T use select * or select table.* in a UNION / UNION ALL query.

* the ORDER BY does not "know" about table names, just the output column name:

SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 2 --khris.roberts
 
UNION ALL

SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and not assignedto_userid = 0 and not assignedto_userid = 2

UNION ALL

SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 0
ORDER BY CallDate

I know about the dashes - it was my mistake before posting

angelIII:,
On each block, the resultset needs to be sorted by CallDate

SELECT * FROM (SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 2 --khris.roberts
ORDER BY CallLogs.CallDate) a
 
--UNION ALL

SELECT * FROM (SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and not assignedto_userid = 0 and not assignedto_userid = 2
ORDER BY CallLogs.CallDate) b

etc.

samopal:

Server: Msg 1033, Level 15, State 1, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Server: Msg 1033, Level 15, State 1, Line 15
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Here's the orginal query.

SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  INNER JOIN Markets ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2
AND      (CallLogs.AssignedTo_UserID = 0
OR       CallLogs.AssignedTo_UserID = @currentUserID)
ORDER BY CallLogs.CallDate

>On each block, the resultset needs to be sorted by CallDate
you will have to do like this:

SELECT 1 as block, Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 2 --khris.roberts
 
UNION ALL

SELECT 2 , Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and not assignedto_userid = 0 and not assignedto_userid = 2

UNION ALL

SELECT 3 , Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 0

ORDER BY block, CallDate


or like this if you don't want the additional column:

select MarketID, MarketName, callDate, <etc...>
FROM (
SELECT 1 as block, Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 2 --khris.roberts
 
UNION ALL

SELECT 2 , Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and not assignedto_userid = 0 and not assignedto_userid = 2

UNION ALL

SELECT 3 , Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2 and assignedto_userid = 0

) l
ORDER BY block, calldate

angelIII,

I I agree this is great, however  - Please, They need to be sorted by CallDate

SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2
ORDER BY CASE
    WHEN assignedto_userid = 2 THEN 0
    WHEN assignedto_userid = 0 THEN 2
    ELSE 1 END ASC
, CallDate

Still not Sorting BY CallDate

if first sorts by the "blocks" you defined, and only then by calldate...

I meant this query

SELECT Markets.MarketID, Markets.MarketName, CallLogs.*
FROM CallLogs  
INNER JOIN Markets
ON CallLogs.MarketID = Markets.MarketID
WHERE CallLogs.CurrentStatusID < 2
ORDER BY CASE
    WHEN assignedto_userid = 2 THEN 0
    WHEN assignedto_userid = 0 THEN 2
    ELSE 1 END ASC
, CallDate

what data type is the field calldate?
can you show some data sample of what you get as output?

DateTime