How to Request a Random Row in Sql

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

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

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.

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())

How can I get random rows in MySQL (NO autoincrement)?

If your ids are truly random, you can just pick a random value and find the first id greater than or equal to that. And if your random value happens to be greater than any ids in the table, try again.

Ideally you pick the random value in your code, but unhex(md5(rand())) is a quick hack that should produce a random 16 byte string:

select id
from yourtable
where id >= unhex(md5(rand()))
order by id
limit 1

PHP - Select random row from table

One method uses a subquery:

SELECT t.*
FROM (SELECT id
      FROM mytable 
      ORDER BY date DESC
      LIMIT 10
     ) t
ORDER BY rand()
LIMIT 1;

This version uses the syntax conventions for MySQL.

You can use select * in the subquery to fetch the whole row.

SQL query on how do I get a random record from another table into a query on another table for every row?

The problem is that NEWID() is only being executed once per the whole query. There is nothing to stop SQL Server doing that, as the subquery is not correlated. To fix this, you can correlate the subquery.

To explain:

NEWID() is a weird function. Although it is a side-effecting function, SQL Server does not guarantee that it will return a new value per output row, unless either the NEWID() call is directly in the main SELECT, or it is somehow correlated to the outer part of the query.

In your query, the inner subquery is not correlated, so the compiler understands that it can hoist it out of the rest of the query. To prevent that, you can force it to calculate it on each outer row, by putting in a redundant correlation. For example:

SELECT
  Lastname,
  (
    select top (1)
      Firstname
    from [dbo].[Firstname_temp] f
    where l.Lastname = l.Lastname + ''
    order by newid()
  ) as Firstname
FROM [TestDatabase].[dbo].[Lastname_temp] l

---

Related Topics