SQL Server union not sorting correctly

Question

I have the following SQL statement:

select top 1 scrr.name, sess.end_time
from tbl_session sess
     inner join tbl_scripts scrr on sess.script_id = scrr.script_id
where end_time >= '5-May-2015 14:58:00'
  and end_time < '06-May-2015 14:58:00'
  and scrr.script_type in (1,3,4)
  and sess.operator_id = 95
UNION
select top 1 scr.name, oh.end_time
from tbl_outbound_history oh
     inner join tbl_outbound o on oh.outbound_id = o.outbound_id
     inner join tbl_session ses on o.session_id = ses.id
     inner join tbl_scripts scr on ses.script_id = scr.script_id
where oh.end_time >= '5-May-2015 14:58:00'
  and oh.end_time < '06-May-2015 14:58:00'
  and oh.start_time <> oh.end_time
  and oh.user_id = 95
order by end_time

The data is not changing, but the result is different every time. It's as though the order by is being ignored.

The second select returns nothing in this case and I have tried to simplify the SQL to minimise the problem, but can't reproduce it.

If I drop the second select, I get the same result every time, so something about a union to nothing and top 1 perhaps though I can't get it to do it with simpler selects.

Anybody come across such a bug before and if so, is there a workround for it.

Any pointers in the right direction would be appreciated.

I'm using:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
    Oct 19 2012 13:38:57 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Answer 1

You need to order & use top 1 in outer query

select top 1 from (
select scrr.name, sess.end_time
from tbl_session sess
     inner join tbl_scripts scrr on sess.script_id = scrr.script_id
where end_time >= '5-May-2015 14:58:00'
  and end_time < '06-May-2015 14:58:00'
  and scrr.script_type in (1,3,4)
  and sess.operator_id = 95
UNION
select scr.name, oh.end_time
from tbl_outbound_history oh
     inner join tbl_outbound o on oh.outbound_id = o.outbound_id
     inner join tbl_session ses on o.session_id = ses.id
     inner join tbl_scripts scr on ses.script_id = scr.script_id
where oh.end_time >= '5-May-2015 14:58:00'
  and oh.end_time < '06-May-2015 14:58:00'
  and oh.start_time <> oh.end_time
  and oh.user_id = 95
) a
order by a.end_time

Also i believe, casting datetime would be great

CAST('06-May-2015 14:58:00' as datetime)