beginnersbook.com

  • Home
  • All Tutorials
    • Learn Servlet
    • Learn JSP
    • Learn JSTL
    • Learn C
    • Learn C++
    • Learn MongoDB
    • Learn XML
    • Learn Python
    • Learn Perl
    • Learn Kotlin
  • Core Java
  • OOPs
  • Collections
  • Java I/O
  • JSON
  • DBMS

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;

Enjoyed this post? Try these related posts

  1. SQL – Combining AND, OR and NOT together in where clause
  2. SQL SELECT AS – Alias in SQL
  3. SQL SELECT DISTINCT Statement
  4. SQL INSERT INTO SELECT Statement
  5. SQL NULL Check in Where clause – IS NULL and IS NOT NULL
  6. NOT NULL Constraint in SQL

Leave a Reply Cancel reply

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

SQL Tutorial

  • SQL Introduction
  • SQL Syntax
  • 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

Recently Added..

  • JSON Tutorial
  • Java Regular Expressions Tutorial
  • Java Enum Tutorial
  • Java Annotations Tutorial

Copyright © 2012 – 2021 BeginnersBook . Privacy Policy . Sitemap