How to Randomly Select Rows in Sql

How to randomly select rows in SQL?

SELECT TOP 5 Id, Name FROM customerNames
ORDER BY NEWID()

That said, everybody seems to come to this page for the more general answer to your question:

Selecting a random row in SQL

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select a random row with sqlite:

SELECT column FROM table 
ORDER BY RANDOM() LIMIT 1

How to request a random row in SQL?

See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select n random rows from SQL Server table

select top 10 percent * from [yourtable] order by newid()

In response to the "pure trash" comment concerning large tables: you could do it like this to improve performance.

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

The cost of this will be the key scan of values plus the join cost, which on a large table with a small percentage selection should be reasonable.

MySQL select 10 random rows from 600K rows fast

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order-by-rand/

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

How to select random rows in tsql with priorities for some rows

You can add a specific component to the order by clause that puts the appropriate phone numbers first e.g.,

SELECT TOP @X column 
FROM table 
ORDER BY 
    CASE WHEN [Phone Number] IS NOT NULL THEN 0 ELSE 1 END,
    NEWID();

This sorts all those with phone numbers in one bucket (0) and those without phone numbers into another bucket (1). They are then still randomised within that bucket.

How do I select random rows from a table with limit and no duplicates?

One method is to pass an argument to RAND() - that is called the seed. As explained in the documentation:

One implication of this behavior is that for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values.

So, consider, for example:

SELECT * FROM table_name ORDER BY RAND(123) LIMIT 100, 101;

The sort is repeatable as long as you give the same seed. Just make sure to change the seed when you start a sequence of searches.

Select random records with no duplicates

You may use ROW_NUMBER here with a random ordering:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Associate ORDER BY NEWID()) rn
    FROM yourTable
)

SELECT Associate, TrackingID
FROM cte
WHERE rn <= 3;

Select a random row from each group SQL Server

select top 1 with ties id,code,age 
from
table
order by row_number() over (partition by id order by rand())

Update: as per this Return rows in random order, you have to use NEWId,since RAND() is fixed for the duration of the SELECT on MS SQL Server.

 select top 1 with ties id,code,age 
 from
 table
order by row_number() over (partition by id order by NEWID())

Best way to randomly select rows *per* column in SQL Server

ORDER BY NEWID() works with ROW_NUMBER in SQL Server 2008. Not sure about SQL Server 2005,

This is needed to generate values to join the 3 separate queries: it's slightly counter intuitive because you'd think it would always take the first 100 rows in a different order but it doesn't...

;With F AS
(
  SELECT TOP 100
    FirstName, ROW_NUMBER() OVER (ORDER BY NEWID()) AS Foo
  FROM Customer
), L AS
(
  SELECT TOP 100
    LastName, ROW_NUMBER() OVER (ORDER BY NEWID()) AS Foo
  FROM Customer
), S AS
(
  SELECT TOP 100
    State, ROW_NUMBER() OVER (ORDER BY NEWID()) AS Foo
  FROM Customer
)
SELECT
   F.FirstName, L.LastName, S.State
FROM
   F
   JOIN L ON F.Foo = L.Foo
   JOIN S ON F.Foo = S.Foo

Select rows randomly without changing the order in sql query

Select the random rows and then re-order them:

select t.*
from (select *
      from table t
      where type = 1
      order by rand()
      limit 25
     ) t
order by datecol;

In SQL, if you want rows in a particular order, you need to use an explicit order by clause. You should never depend on the ordering of results with no order by. SQL does not guarantee the ordering. MySQL does not guarantee the ordering, unless the query has an order by.

---

Related Topics