5 Ways to Get Random Records in MySQL with ORDER BY RAND

MySQL RAND() function provides best methods to select random rows from the database table. MySQL select random records using ORDER BY RAND()

You can get random single records from the mysql database using a without join SQL query in MySQL.

SELECT * FROM Products_data
ORDER BY RAND()
LIMIT 1;

MySQL ORDER BY rand(), name ASC

Use a subquery:
in this example learn to “Order MySQL records randomly and display name in Ascending order”.

SELECT * FROM
(
SELECT * FROM members ORDER BY rand() LIMIT 10
) T1
ORDER BY name

MySQL Select Random Records

SELECT
memberNumber,
memberName
FROM
members
ORDER BY RAND()
LIMIT 5;

MySQL select random records using INNER JOIN clause

query generates a random number

SELECT
ROUND(RAND() * ( SELECT MAX(id) FROM your_table_name)) AS id;

Example

SELECT
t.memberNumber, t.memberName
FROM
members AS t
JOIN
(SELECT
ROUND(RAND() * (SELECT
MAX(memberNumber)
FROM
members)) AS memberNumber
) AS x
WHERE
t.memberNumber >= x.memberNumber
LIMIT 1;

MySQL select random records using variables

SELECT
table. *
FROM
(SELECT
ROUND(RAND() * (SELECT
MAX(id)
FROM
table)) random_num,
@num:=@num + 1
FROM
(SELECT @num:=0) AS a, table
LIMIT N) AS b,
table AS t
WHERE
b.random_num = t.id;

Randomly ordering a MySQL result set

SELECT member_name, profile, member_email, mobile
FROM members
ORDER BY RAND()
LIMIT 10

SQL ORDER BY RANDOM

Oracle

SELECT
author||' - '||title AS article
FROM article
ORDER BY DBMS_RANDOM.VALUE

using SQL Server

SELECT
CONCAT(CONCAT(author, ' - '), title) AS article
FROM article
ORDER BY NEWID()

using PostgreSQL

SELECT
author||' - '||title AS article
FROM article
ORDER BY random()

using MySQL

SELECT
CONCAT(CONCAT(author, ' - '), title) AS article
FROM article
ORDER BY RAND()

using JPA and Hibernate

List<Article> articles = entityManager
.createQuery(
"SELECT a " +
"FROM Article a " +
"ORDER BY random()", Article.class)
.getResultList();

Results

How to Select Random Records in MySQL with Codeigniter 3?

$this->db->order_by('id','RANDOM');
$data = $this->db->get("members");

Example 2:
Codeigniter 3 – Order by random records from mysql database example

$this->db->order_by(47,'RANDOM');
$data = $this->db->get("members");

I hope you get an idea about mysql order by rand.

• This blog post was originally published at: https://www.pakainfo.com

#mysql  #sql  #database