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