BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

SQL Select Random Rows from Table

By Chaitanya Singh | Filed Under: SQL

Sometimes there is a need to fetch random record from the table. For example – You have a list of quotes stored in a table and you would like to display a random quote on GUI, in such case you would have to write an SQL query to fetch random record from a table of quotes. In this tutorial, we will see how to select a random record from a table using RAND function.

Real World examples:
1. Online exams, where you want to display a random question.
2. For an e-commerce website to display random featured items on home page.
3. Display a random featured image on a website

Selecting random rows from table in MySQL

Syntax:
Here N specifies the number of random rows, you want to fetch. For example: If you want to fetch only 1 random row then you can use the numeric 1 in place N.

SELECT column_name FROM table_name
ORDER BY RAND()  
LIMIT N;

Example: When we forget the passwords, the system asks the random security questions to verify the identity. Lets take the same example. Here we have a table, where we have security questions stored and we are fetching a random question from the table.

The following SQL statement will fetch 1 random record from the table Questions

SELECT QUESTION
FROM Questions
ORDER BY RAND()  
LIMIT 1;

Table: Questions

QNO    QUESTION
----   ---------
1      What is your pet’s name?
2      What is your favourite food?
3      In what year was your mother born?

Result:

What is your favourite food?

Note: The output may be different for you because the question is randomly selected. Also, when you run this command again, you may get a different output.

Select random row from table in PostgreSQL

To do the same thing in PostgreSQL, we use RANDOM() function instead of RAND().

SELECT column_name 
FROM table_name
ORDER BY RANDOM()
LIMIT 1;

Select random row from table in Microsoft SQL Server

SELECT TOP 1 column_name 
FROM table_name
ORDER BY NEWID();

Select random row from table in Oracle

SELECT column_name
FROM
( SELECT column_name 
  FROM table_name
  ORDER BY dbms_random.value 
)
WHERE ROWNUM = 1;

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Copyright © 2012 – 2022 BeginnersBook . Privacy Policy . Sitemap