question

GraGra33 avatar image
GraGra33 asked

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

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   Listings AS L2                    
             WHERE  L2.listing_id IN                     
                 (SELECT TOP 100 PERCENT L3.listing_id                    
                  FROM   Listings AS L3                    
                  WHERE  (L3.user_id = L.user_id)                    
                 )                    
             ORDER BY NEWID()                    
            )                    
       )                    
ORDER BY Shops.shop_name                    

I’m stumped. Anyone have any ideas on how to fix it?

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?

sql-server-2005sub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GraGra33 avatar image
GraGra33 answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3ley dein avatar image
3ley dein answered

SELECT user_id, listing_id FROM
( SELECT l.user_id, l.listing_id, RowNumber = ROW_NUMBER() OVER (PARTITION BY l.user_id ORDER BY NEWID()) FROM Listings l INNER JOIN (SELECT user_id FROM Listings GROUP BY user_id HAVING COUNT(*) >= 3 ) cnt ON cnt.user_id = l.user_id
) l
WHERE l.RowNumber <= 3

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.