MSSQL - Select one random record not showing duplicates
- by Lukes123
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