Q:

Select a random database row based on another query

For internal control we would like to select a single random invoice for each of multiple invoice types and regions. 
Here's the SQL to get a set of distinct Invoice Types and Regions
  select InvoiceType,RegionID 
  from Invoices 
  group by InvoiceType, RegionID 

For each row this returns I need to fetch a random row with that InvoiceType and RegionID. This is how I'm fetching random rows:
SELECT top 1 
   CustomerID
  ,InvoiceNum
  ,Name
  FROM Invoices
  JOIN Customers on Customers.CustomerID=Invoices.CustomerID
 where InvoiceType=X and RegionID=Y
ORDER BY NEWID

But I don't know how to run this select statement foreach() row the first statement returns. I could do it programmatically but I would prefer an option using only a stored procedure as this query isn't supposed to need a program.

A:

WITH cteInvoices AS (
    SELECT CustomerID, InvoiceNum, Name,
           ROW_NUMBER() OVER(PARTITION BY InvoiceType, RegionID ORDER BY NEWID()) AS RowNum
        FROM Invoices
)
SELECT c.CustomerID, c.InvoiceNum, c.Name
    FROM cteInvoices c
    WHERE c.RowNum = 1;

