Posted by JMoore2026@gmail.com on 06/08/06 23:40

thanks guys... i got it to work using this method. This table should
not end up being very large and i'm not to worried about the cost of
this method... If anyone has a more efficient and presice method though
let me know...
Thanks
Jeremy
Jmoore2026@gmail.com


Erland Sommarskog wrote:
> JMoore2026@gmail.com (JMoore2026@gmail.com) writes:
> > i found that this does not get a random value... it only returns the
> > first record... how would i use this and return a random value.
>
> select col1, col2, col3, col4, col5, col6
> from table1
> where col1 = @variable1 and col2 = @variable2
> union
> select col1, col2, col3, col4, col5, col6
> from (SELECT TOP 1 col1, col2, col3, col4, col5, col6
> from table2
> where col2 = @variable2
> order by newID()) AS d
>
> You need a derived table into which you can put the TOP 1 and ORDER BY.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Tough SQL problem, need expert advice!!!
• Prev in forum: composite index and column order
• Thread view: Re: SQL Union Problems When Trying to Retrieve Random Records

[Reply to this message]