Query to Return N Random Concatenated Records

Question

I was handed a task where I need to return a list of all the companies from one table and a comma-separated column of 3 random email addresses associated with that company from another table using Microsoft SQL Server. Thus far I have been able to get the companies and all associated email addresses in random order, but I now need to limit this list to 3. Every company will have a minimum of 4 associated email addresses but could conceivably have hundreds. Here is my SQL as is.

SELECT c.ID, c.companyName, 
STRING_AGG(e.emailAddress, ', ') WITHIN GROUP (ORDER BY NEWID()) AS eMailAddress
FROM companies c
INNER JOIN email e ON e.companyID = c.ID
GROUP BY c.ID, c.companyName

I'd appreciate any thoughts on this.

Answer 1

Just CROSS APPLY 3 email addresses on:

SELECT c.ID, c.companyName, 
STRING_AGG(e.emailAddress, ', ') AS eMailAddress
FROM companies c
CROSS APPLY (
    SELECT TOP 3 *
    FROM email e
    WHERE e.companyID = c.ID
    ORDER BY NEWID()
) e
GROUP BY c.ID, c.companyName;