SQL Server Performance

random selection of rows from a table

Discussion in 'Getting Started' started by kumar_kisna, May 10, 2007.

  1. kumar_kisna New Member

    if there are 100 rows in a table and we have to select 10 random rows
    in such a way that every time we fire the query , the result set has
    new 10 randomly selected rows....
  2. MohammedU New Member

    Try the following...
    SELECT TOP 10 * FROM TableName ORDER BY NEWID()




    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. Adriaan New Member

    In T-SQL, if you don't use an ORDER BY clause, you always have a chance of getting the results in random order. This means you are already getting random rows by not specifying any form of ORDER BY.

    Problem is that this randomness is not considered to be random enough, because a lot of the time you will see the same rows, more often than not returned in the same order. But strictly speaking, using ORDER BY NEWID() is overkill.

    Instead of "randomly selected rows", the assignment should have asked for "unpredictably selected rows".
  4. sshelper New Member

  5. Adriaan New Member

    I wasn't trying to say MohammedU's solution is not the correct one - just making an observation.<br /><br />Note that I qualified it by "strictly speaking".[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  6. kumar_kisna New Member

    mohammad solution is working...
  7. Asif Ali New Member

    Do you receive any message that row size is ended and no more records entered?
    When you update any row in enterprise manager in physical table, is there any option to roll back as auto commit is on in SQL server or you physically select the row and update information?
    Please solve these issues.
  8. satya Moderator

    Asif
    Have you read Frank's reply in your threadhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21395 and it seems you haven't got a complete understanding and there will be no use in hijacking others thread with your problem.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page