Distinct records and NEWID()

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

Re: Distinct records and NEWID()

"Adrienne Boswell" wrote in message
news:1178832757.546818.300920@q75g2000hsh.googlegroups.com.. .
> 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()

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

-Mark


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

Re: Distinct records and NEWID()

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

>
> "Adrienne Boswell" wrote in message news:1178832757.546818.300920@q75g2000hsh.googlegroups.com.. .
>> 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

Re: Distinct records and NEWID()

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

Re: Distinct records and NEWID()

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

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

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