Get x random rows from SQL Server

Question

I have read this question: What is the best way to get a random ordering? and can do a select top 4 from tabel order by NEWID() but I need it per category. So I need 1 row from category A, 1 row from category B, 1 row from category C and 1 row from parentcategory D.

So I tried to union them and I tried to add a wrapping select query which told me that I could not order the subquery... So I am stuck.

Here is the SQL I have so far:

SELECT top 4 Artikel.ArtikelID -- etc.
FROM Artikel 
INNER JOIN Categorie ON Artikel.CategorieID = Categorie.CategorieID 
INNER JOIN CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID 
INNER JOIN CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
INNER JOIN CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
INNER JOIN Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
INNER JOIN Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID 
WHERE (ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
and taal.taalcode = 'EN'
order by NEWID()

current result could contain 4 rows all of the same category. That is not what I want. It's a bit different than the possible duplicate because it is not grouped on 1 column, but two different once.

• Mark Sinkinson: Related: Retrieving n rows per group
• JP Hellemons: I have used the ROW_NUMBER() OVER (PARTITION before but I do not know what the best approach is, since the fourth row should be from a certain parentcategory instead of normal category. I have never looked into Apply so will do that asap.

Answer 1

Let's simplify the problem because I think it will be easier to visualize when you don't start adding in the complication of joins and other clauses.

Let's think about objects in the system catalog sys.all_objects, and say you want one of each of the following object types in the resultset:

• table ('U')
• procedure ('P')
• view ('V')
• any type of function ('FN', 'IF', 'FS', 'AF', 'TF')

The whole set is simply:

SELECT * FROM sys.all_objects 
WHERE type IN (N'U',N'P',N'V',N'FN',N'IF',N'FS',N'AF',N'TF');

To get a single, random row from each type (or set of types), you can say:

;WITH wholeset AS 
(
 SELECT *, rn = ROW_NUMBER() OVER 
 (
 PARTITION BY CASE 
 WHEN [type] IN (N'FN',N'IF',N'FS',N'AF',N'TF') 
 THEN N'FN'
 ELSE
 [type]
 END ORDER BY NEWID()
 )
 FROM sys.all_objects 
 WHERE type IN (N'U',N'P',N'V',N'FN',N'IF',N'FS',N'AF',N'TF')
)
SELECT * FROM wholeset WHERE rn = 1;

Applying similar logic to your query, assuming -1 is not a valid category, in which case you could use -999 or some other impossible token value:

;WITH wholeset AS
(
 SELECT ...cols..., rn = ROW_NUMBER() OVER
 (
 PARTITION BY CASE 
 WHEN Categorie.ParentCategorie = 292 THEN -1 
 ELSE Artikel.Categorie 
 END ORDER BY NEWID()
 )
 FROM dbo.Artikel -- always use dbo prefix
 INNER JOIN dbo.Categorie ON Artikel.CategorieID = Categorie.CategorieID 
 INNER JOIN dbo.CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID 
 INNER JOIN dbo.CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
 INNER JOIN dbo.CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
 INNER JOIN dbo.Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
 INNER JOIN dbo.Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID 
 WHERE (Categorie.ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
 AND taal.taalcode = 'EN'
)
SELECT * FROM wholeset WHERE rn = 1;

There are some edge cases here, like if you don't have any rows in a category, or if you only have one row that is in the parent category and that is the only row in one of the other three categories as well. But for most data sets this should work pretty reliably.

Answer 2

A solution (it's perhaps not THE solution, but it gives me the expected results)

SELECT top 1 WITH TIES Artikel.ArtikelID -- etc.
FROM Artikel 
INNER JOIN Categorie ON Artikel.CategorieID = Categorie.CategorieID 
INNER JOIN CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID 
INNER JOIN CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
INNER JOIN CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
INNER JOIN Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
INNER JOIN Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID 
WHERE (ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
and taal.taalcode = 'EN'
Order by Row_Number() over (Partition By case when artikel.CategorieID in (251,252,255,265,269,273,285,289,290,327) then 'C'
 else 
 case when artikel.CategorieID = 246 then 'q' else 
 case when artikel.CategorieID = 247 then 'P' else 
 case when artikel.CategorieID = 288 then 'L' else null end 
 end
 end
 end Order By NewID())