Posted by Erland Sommarskog on 06/06/06 21:47

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: Re: Challenge: Can you optimize this?
• Thread view: Re: SQL Union Problems When Trying to Retrieve Random Records

[Reply to this message]