Return Rows in Random Order

Return rows in random order

SELECT * FROM table
ORDER BY NEWID()

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 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.

Select rows in random order and then reverse it

You have to "cache" the results of the random ORDER BY.

In this code, if you refer to the CTE in the UNION it will be evaluated twice and you'll have 2 different orders. A CTE is just a macro

;WITH cList AS
(
SELECT team, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM teams
)
SELECT * INTO #tempresults FROM cList WHERE rn <= @rn --or however many

SELECT *, rn FROM #tempresults
UNION ALL
SELECT *, (2 * @rn) - rn FROM #tempresults
ORDER BY rn

Duplicating rows is easy with a dummy cross join (like this) but this requires ordering and rownumbering too over the intermediate results. I don't think it can be done in a single SQL statement

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

Displaying records in random order

You'd be better off selecting all the entries and leaving the random display to php. The whole ORDER BY RAND() requires a lot of resources. The ORDER BY RAND() operation actually re-queries each row of your table, assigns a random number ID and then delivers the results.

$sql = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 1");

But to my knowledge this just selects a random number of records like 20.

Wrong: This will select all records then rearrange them "randomly" and give you the first row: LIMIT 1, just leave it out.

$sql = mysql_query("SELECT * FROM table ORDER BY RAND()");

---

Related Topics