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.

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())