How can I modify this to get a random record on the many end of a join?

Question

Here is my query:

    Select Top 10 CS.CaseStudyID,
    CS.Title,
    CSI.ImageFileName
From CaseStudy CS
Left Join CaseStudyImage CSI On CS.CaseStudyID = CSI.CaseStudyID
And CSI.CSImageID in(
    Select Min(CSImageID) -- >not really satisfactory
    From CaseStudyImage
    Group By CaseStudyID
    )
Order By CS.CaseStudyID ASC

Instead of min(CSImageID) I'd like a random record from my CaseStudyImage table that corresponds to the particular case study

Can anyone point me in the right direction pleas?

Answer 1

ORDER BY RAND() LIMIT 1?

Answer 2

This article describes 3 techniques for random sampling in t-sql. It depends on how much control you have over your table structure one of them might do want you want.

Answer 3

You can use ranking function and newid() to create randomize order with grouping.

WITH CSI AS (
    SELECT CSI.CaseStudyID, CSI.ImageFileName,
     ROW_NUMBER() OVER(PARTITION BY CSI.CaseStudyID ORDER BY newid()) AS RowNumber
    FROM CaseStudyImage CSI
)
SELECT TOP (10) CS.CaseStudyID, CS.Title, CSI.ImageFileName
FROM CaseStudy CS LEFT JOIN CSI On CS.CaseStudyID = CSI.CaseStudyID
WHERE CSI.RowNumber = 1
ORDER BY CS.CaseStudyID ASC

Answer 4

usually, just ORDER BY NEWID() does the trick