inetserver asp db: 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()

Here are my results:
id vendor image
402 432
storitallstoritallr.jpg
461 39
fivestar.jpg
366 772165
772165_C1_toolmart.gif
272 7581
silentsilent.gif
432 6
adelvacancy.gif
363 2931
gennaroGennarobar.jpg
487 39
fivestar.jpg


Notice ids 487 and 461 are exactly the same, except for the id
number. If I put a SELECT DISTINCT on it, then I cannot ORDER BY
NEWID(), because NEWID() would have to be in the SELECT list, and
since NEWID() IS random, I would still be at my same problem.

Basically, I need for the images to be random and distinct. Any ideas?

TIA
--
Adrienne Boswell at work
Administrator nextBlock.com
http://atlas.nextblock.com/files/
Please respond to the group so others can share

[quoted text, click to view]

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()

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

-Mark


[quoted text, click to view]

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

[quoted text, click to view]

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

Oops, didn't notice this one was 3 weeks old ...

Gazing into my crystal ball I observed "Daniel Crichton"
<msnews@worldofspack.com> writing in news:e7Z$uDFqHHA.4396
@TK2MSFTNGP02.phx.gbl:

[quoted text, click to view]

That's quite all right. I'm still muddling along with my queries, so this
came at a good time. Thanks!

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share