SQL Server - Select one random record not showing duplicates

Question

I have two tables, events and photos, which relate together via the 'Event_ID' column. I wish to select ONE random photo from each event and display them.

How can I do this?

I have the following which displays all the photos which are associated. How can I limit it to one per event?

SELECT Photos.Photo_Id, Photos.Photo_Path, Photos.Event_Id, Events.Event_Title,
   Events.Event_StartDate, Events.Event_EndDate FROM Photos, Events 
WHERE 
   Photos.Event_Id = Events.Event_Id AND 
   Events.Event_EndDate < GETDATE() AND 
   Events.Event_EndDate IS NOT NULL AND
   Events.Event_StartDate IS NOT NULL
ORDER BY NEWID()

Thanks

Luke Stratton

Answer 1

You could use a cross apply to retrieve one random photo per event:

select *
from Events
cross apply (
    select top 1 *
    from Photos
    where Photos.Event_Id = Events.Event_Id
    order by newid()
) RandomPhoto
where Events.Event_EndDate < GETDATE()
and Events.Event_EndDate IS NOT NULL
and Events.Event_StartDate IS NOT NULL

Use an outer apply if you'd like to retrieve events without any photos.

Answer 2

Look at this xaprb article. It shows several techniques you could use. It recommends a minimal-row and self-join approach.