|
|
Posted by Greg D. Moore \(Strider\) on 06/09/06 03:31
<JMoore2026@gmail.com> wrote in message
news:1149810018.008320.252930@h76g2000cwa.googlegroups.com...
> 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...
I don't, but one I've wanted to try in SQL 2005 (but haven't had time) is to
use ROW_NUMBER to number my results, get a max count and then use the random
function to select from that range.
> 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
>
Navigation:
[Reply to this message]
|