Skip navigation

Catch That Bug! Random Row per Group

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

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish