Selecting random top 3 listings per shop for a range of active advertising shops

[Graeme_Grant]

I’m trying to display a list of shops each with 3 random items from their shop, if they have 3 or more listings, that are actively advertising. I have 3 tables: one for the shops – “Shops”, one for the listings – “Listings” and one that tracks active advertisers – “AdShops”. Using the below statement, the listings returned are random however I’m not getting exactly 3 listings (rows) returned per shop.

SELECT AdShops.ID, Shops.url, Shops.image_url,
Shops.user_name AS shop_name, Shops.title, L.listing_id AS listing_id,
L.title AS listing_title, L.price as price,
L.image_url AS listing_image_url, L.url AS listing_url

FROM AdShops INNER JOIN
Shops ON AdShops.user_id = Shops.user_id INNER JOIN
Listings AS L ON Shops.user_id = L.user_id

WHERE (Shops.is_vacation = 0 AND Shops.listing_count > 2 AND
L.listing_id IN
(SELECT TOP 3 L2.listing_id
FROM Etsy_Listings AS L2
WHERE (L2.user_id = L.user_id)
ORDER BY NEWID()
)
)
ORDER BY Shops.shop_name

When 'ORDER BY NEWID()' is removed, I get exactly 3 rows returned per shop but it's the first 3 listings and not random - I’m stumped. :mad: Anyone have any ideas on how to fix it? :doh: The ideal solution would be one record per store with the 3 listings (and associated data) were in columns and not rows – is this possible? :cool:

[Graeme_Grant]

Thanks to Lieven :thumbsup: stackoverflow.com[stackoverflow.com] for the key to solving the problem. Full solution is as follows:

SELECT AdShops.ID, Shops.url, Shops.image_url,
Shops.user_name AS shop_name, Shops.title, L.listing_id AS listing_id,
L.title AS listing_title, L.price as price,
L.image_url AS listing_image_url, L.url AS listing_url
FROM AdShops INNER JOIN
Shops ON AdShops.user_id = Shops.user_id INNER JOIN
Listings AS L ON Shops.user_id = L.user_id
WHERE (Shops.is_vacation = 0 AND Shops.listing_count > 2 AND
L.listing_id IN
(SELECT listing_id
FROM
(SELECT l2.user_id , l2.listing_id, RowNumber = ROW_NUMBER()
OVER (PARTITION BY l2.user_id ORDER BY NEWID())
FROM Listings l2 INNER JOIN
(SELECT user_id
FROM Listings
GROUP BY user_id
HAVING COUNT(*) >= 3
) cnt ON cnt.user_id = l2.user_id
) l2
WHERE l2.RowNumber <= 3 and L2.user_id = L.user_id
)
)
ORDER BY Shops.shop_name

Anyone have any ideas on how to merge the 3 rows into a single row with the 3 listings moved into columns (several colums per listing) ???? :doh:

[Mycroft Holmes]

Here we go again, this article may be of use Pivot two or more columns in SQL Server 2005[^] This query basically represents the "inner" query in the article, now you need to extract the column labels to be pivotted on and write the outer query. I would stuff this query into a table var or view and work from there.

Never underestimate the power of human stupidity RAH