Re: Distinct records and NEWID()

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Adrienne Boswell" <arbpen@xxxxxxxxx> wrote in message news:1178832757.546818.300920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Using SQL 7 and classic ASP -

Here's my query:
SELECT TOP 7 f.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' ORDER BY NEWID()

Maybe this would work?

--------------

SELECT id, vendor_ipk, featured_image_path
FROM (
SELECT TOP 7 DISTINCT f.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'
) drs ORDER BY NEWID()

--------------


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


.

Relevant Pages

  • Re: Distinct records and NEWID()
    ... Here's my query: ... FROM featuredselection f, view_vendor_default v WITH ... drs ORDER BY NEWID() ...
    (microsoft.public.inetserver.asp.db)
  • Distinct records and NEWID()
    ... Using SQL 7 and classic ASP - ... Here's my query: ... featuredselection f, view_vendor_default v WITHWHERE ... NEWID(), because NEWIDwould have to be in the SELECT list, and ...
    (microsoft.public.inetserver.asp.db)