SQL to Select Random Rows from Table

Selecting Random Rows from Application Layer is always a mess. After some googling, I find those SQL Commands to SELECT Random Rows in different Databases. Here, I just put those Commands together for convenience.


-- SQL Server
SELECT TOP 1 * FROM TABLE_NAME
ORDER BY NEWID()



-- MS Access
SELECT TOP 1 *
FROM TABLE_NAME
ORDER BY RND(INT(NOW()*TABLE_IDENTITY_FILED)- NOW()*TABLE_IDENTITY_FILED);


-- MySQL
SELECT * FROM TABLE_NAME
ORDER BY RAND()
LIMIT 1


-- Oracle
SELECT * FROM
( 
 SELECT column FROM TABLE_NAME
 ORDER BY dbms_random.value 
)
WHERE rownum = 1

Thanks
A Rahim Khan

Advertisements
  1. An even faster way of getting some random rows from an Oracle table is to use the SAMPLE keyword:

    SELECT * FROM TABLE_NAME SAMPLE(1);

    This example will return approximately 1% of the rows from the table.

    • Thanks for informing us. Can SAMPLE keyword return fixed number of rows? like just fixed 4/5 no of rows.

      • Not exactly. The SAMPLE keyword effectively “flips a coin” for each row returned by the row source. For example, if you use SAMPLE(50), if the coin comes up “heads”, the row is returned. If the coin comes up “tails”, the row is not returned. This means on average you should get approximately 50% of the rows in the table. However, it’s never guaranteed to return any fixed number of rows. It’s theoretically possible to come up “tails” on every single row – however this is rare. You can approximate your desired behaviour by querying a small sample, and if you don’t get enough rows, re-run the query until you do. This is perhaps not as statistically valid as using DBMS_RANDOM, so you have to pick the best tool for your particular requirements in each case.

    • parthapratimdhar
    • July 2nd, 2011

    how to i select random number of rows from a table without rand() on mysql?

  2. Hi, only problem is your statement for ms sql is not random, its the last rows added, if you don’t add any more rows you’ll always get the same results. Not exactly random is it.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: