The Orders table in the Northwind database contains orders that were
handled by nine employees. You get a task to write a query that returns a
random order for each employee. You come up with the following query:

                              SELECT EmployeeID, OrderID, OrderDate, CustomerID                              FROM dbo.Orders AS O1                              WHERE OrderID IN                                  (SELECT TOP 1 OrderID                                   FROM dbo.Orders AS O2                                   WHERE O2.EmployeeID = O1.EmployeeID                                   ORDER BY NEWID())                              ORDER BY EmployeeID;                              
But to your surprise, every time you run the query you get a different number
of rows. For some employees you get more than one order, and for some
employees you get no orders. For example, here’s the output that I got in one
of the cases that I ran the query:
                              EmployeeID  OrderID     OrderDate               CustomerID                              ----------- ----------- ----------------------- ----------                              3           10638       1997-08-20 00:00:00.000 LINOD                              3           10700       1997-10-10 00:00:00.000 SAVEA                              3           10712       1997-10-21 00:00:00.000 HUNGO                              3           10410       1997-01-10 00:00:00.000 BOTTM                              5           10899       1998-02-20 00:00:00.000 LILAS                              6           10539       1997-05-16 00:00:00.000 BSBEV                              7           10428       1997-01-28 00:00:00.000 REGGC                              
And here’s the output I got in another case:
                              EmployeeID  OrderID     OrderDate               CustomerID                              ----------- ----------- ----------------------- ----------                              2           10912       1998-02-26 00:00:00.000 HUNGO                              3           10904       1998-02-24 00:00:00.000 WHITC                              3           10436       1997-02-05 00:00:00.000 BLONP                              4           10464       1997-03-04 00:00:00.000 FURIB                              5           10812       1998-01-02 00:00:00.000 REGGC                              7           10868       1998-02-04 00:00:00.000 QUEEN                              8           10565       1997-06-11 00:00:00.000 MEREP                              8           10720       1997-10-28 00:00:00.000 QUEDE                              
Can you explain the bug in the query and suggest a solution?
I’ll give you a week to think about it before I provide the answer.

--
BG