Webmaster Forum / ASP / Database Access / June 2007

Mark wrote  on Fri, 18 May 2007 01:00:25 -0700:

From the results given, f.id is different (at a guess, an identity value),
so DISTINCT here still doesn't remove duplicates. Also the subquery will
just pick 7 rows, which might well be the same ones repeatedly if the data
is read from cache each time as the ORDER BY NEWID() is outside of the
subquery - the repeated 7 rows will likely be in a different order each
time, but it'll still almost always be the same 7. Maybe this:

   SELECT TOP 7 MIN(f.id) as id, v.vendor_ipk, featured_image_path
   FROM featuredselection f, view_vendor_default v WITH(NOEXPAND)
   WHERE f.vendor_id = v.vendor_ipk AND f.status = 'A'
   GROUP BY v.vendor_ipk, featured_image_path
   ORDER BY NEWID()

This gets 7 "random" rows that have a unique vendor_ipk and
featured_image_path combination (so assumes that featured_image_path is
related directly to vendor_ipk, which may not be correct), and the lowest
value of f.id for that combination. It's easy enough to change which f.id
value to return by changing the function used.

Dan