CODEWITHSUNDEEP
sqlsql-server
user2277630
user2277630

Reputation: 3

SQL: Trying to select 1 random row for each category

I have a table which looks like this:

ID       Section        AlbumName        ImageName

1        Section1       Album1           Image1
2        Section1       Album1           Image2
3        Section1       Album2           Image3
4        Section2       Album3           Image4
5        Section3       Album4           Image5

What I'm trying to do is to get 1 random image from each section, I know at the moment I only have multiple images in Album1, this is just for testing.

The SQL that I'm using is

Select Distinct g.albumName,
(select top 1 gl.imageName 
from Gallery gl
where gl.Section = g.Section
and gl.AlbumName = g.AlbumName
order by newid()
) as imageName
from Gallery g

But this is not always giving me the results that I expect. Some times it will return 1 imageName for Album1 and other times it will return both imageNames.

If I run the subquery by itself and substitute g.section with section1 and g.AlbumName with Album1 then only 1 result is returned, so i know it has something to do with the way i'm using the where conditions but I'm just not sure whats wrong.

Can anybody suggest why this would be happening and how I can modify the query to get what I need?

Upvotes: 0

Views: 533

Answers (1)

John Woo
John Woo

Reputation: 263803

This method uses Common Table Expression and Windowing Function.

WITH records 
AS
(
    SELECT  ID, [Section], AlbumName, ImageName,
            ROW_NUMBER() OVER (PARTITION BY [Section] ORDER BY NEWID()) rn
    FROM    TableName
)
SELECT  ID, [Section], AlbumName, ImageName
FROM    records
WHERE   rn = 1

Upvotes: 2

Related Questions