My table contains three fields: ID, ThreadID and Message. From my client, I want to randomly select a row from that table -- however the randomly selected row must have ThreadID = x (i.e. any value). That is, if x = 2, I want to randomly select a row from my table that has ThreadID = 2.
I can think of some brute-force inefficient methods to achieve this. What are your thoughts on some efficient methods for this?
SELECT TOP 1 * FROM
FROM table TABLESAMPLE (1 ROWS)
You need to do the outer select because TABLESAMPLE will not always return exactly the number of records requested.
You can also use with the syntax
TABLESAMPLE (30 PERCENT)
there are some limitations and caveats with this.
the tablesample is done before the where condition is applied, so if none of the records pulled at random will satisfy the where clause then you will get an empty results set. To do what you want to do, you would need to copy the record IDs to a work table, do the tablesample from there, join back to the source table and then delete the work table - you would probably be better off using one of the established randomise processed like newid()
the tablesample cannot be used on an outer query
SELECT TOP 1 A.* FROM
) A TABLESAMPLE (1 ROWS)
You also can't use is on a table function results set
SELECT * FROM tfn_tablefunction(arg1,arg2) TABLESAMPLE (1 ROWS)
Thank you all for your kind comments. I have to confess I had to go back to the book to look it up...I posted because I could find nothing on the web about selecting random lines and everyone seems to have problems with it.
@gvee - OK, u got me there
@r937 - I think I have a way to go to catch you up. When do you find time to acutally do any paid work?