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.
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)