Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Selecting a random row from a table

    I have the following situation:

    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?

    Thanks for any insight or any ideas into this

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT TOP 1
           some
         , fields
    FROM   your_table
    WHERE  threadid = 2
    ORDER
        BY NewId()
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by gvee
    Code:
    SELECT TOP 1
           some
         , fields
    FROM   your_table
    WHERE  threadid = 2
    ORDER
        BY NewId()
    Hmmm... So how does this approach work? What does NewId do in this context?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try it and see
    Code:
    SELECT some
         , fields
         , NewId()
    FROM   your_table
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    NewID generates random GUID values.
    Random enough, anyway,
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2009
    Posts
    2

    Select a random row from a table

    If you are using MSSQL2005/2008 you can use

    Code:
    SELECT TOP 1 * FROM
    (
      SELECT fields
      FROM table TABLESAMPLE (1 ROWS)
      WHERE condition
    )
    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

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


    Code:
    SELECT TOP 1 A.* FROM
    (
      SELECT fields
      FROM table 
      WHERE condition
    ) A TABLESAMPLE (1 ROWS)
    won't work

    You also can't use is on a table function results set

    Code:
    SELECT * FROM tfn_tablefunction(arg1,arg2) TABLESAMPLE (1 ROWS)
    will also result in an error

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    obiron, thank you and welcome

    what a stellar first post!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...as long as he doesn't follow up with a post about EAV, or fact tables in data warehouses, he should be fine.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Should I point out the teeny-weeny mistake in the first bit of code?

    Gotta agree with Rudy - great way to start
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee
    Should I point out the teeny-weeny mistake in the first bit of code?
    It would be churlish to point out he forgot to alias the table when he learntened us so much. I didn't know about TABLESAMPLE.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2009
    Posts
    2
    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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by obiron
    When do you find time to acutally do any paid work?
    in between forum postings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He bills in 2-minute increments.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •