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 REGGCAnd 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 QUEDECan 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
7 comments
Hide comments