How to Request a Random Row in SQLite?

SQLite is a database engine that provides a relational database management system and is a C language library with features like self-contained, serverless, and high reliability. SQLite is different from other traditional SQL database engines like MySQL, Oracle, PostgreSQL, etc. Traditional database engines are client-server based and SQLite is serverless and data is stored in a single file.

SQLite is implemented on small datasets and is used for mobile or desktop applications. The syntax used to write queries and the functions or the datatypes used are almost the same and are similar to what we do in SQL as SQLite is a relational database management system that uses SQL as its query language.

Getting a Random Record in SQLite

Now, to get a random record in SQLite, we can use the below-mentioned methods,

• ORDER BY RANDOM(): This clause is used to order the records in a table randomly and after the records are randomly ordered, we can use the LIMIT clause to retrieve any set of random records from the table.
  
• LIMIT and OFFSET: We can get a random record from a table using the LIMIT and OFFSET keywords and using them in a subquery.

Let's look at the syntax and examples of the above-specified methods,

Setting Up An Environment

Consider the following employee table,

CREATE TABLE Employees (
    id INT,
    name VARCHAR(50),
    position VARCHAR(50),
    dept VARCHAR(50),
    salary INT
);

INSERT INTO Employees (id, name, position, dept, salary) VALUES
(1, 'John Doe', 'Manager', 'HR', 50000),
(2, 'Jane Smith', 'Engineer', 'Engineering', 60000),
(3, 'Mike Johnson', 'Sales Representative', 'Sales', 45000),
(4, 'Emily Brown', 'Accountant', 'Finance', 55000),
(5, 'Alex Davis', 'Administrator', 'Admin', 48000);

Output:

1. ORDER BY RANDOM()

We can order the records randomly by using the ORDER BY RANDOM() function or clause and as we want only one record from the table, we use the LIMIT 1 clause to retrieve only one record from the table.

Syntax:

SELECT * FROM table_name ORDER BY RANDOM() LIMIT num_records_to_return;

Example:

SELECT  * FROM employee ORDER BY RANDOM() LIMIT 1;

Output:

Explanation: You can observe in the above result, that I executed the query multiple times to check if it returning random records or not. So, it's clear that the query is returning a random record every time I execute it. First, it returned the record with id=4 then it returned some other record.

2. LIMIT OFFSET

A LIMIT clause is used to limit the number of records retrieved and the OFFSET clause is used to skips a certain number of rows in the table before returning the result. A random record can be retrieved by combining RANDOM() and LIMIT OFFSET clause, where we can generate a random offset value.

Syntax:

SELECT * FROM employee

ORDER BY RANDOM()

LIMIT num_records_to_return OFFSET num_records_to_skip;

Example:

--to randomly generate offset 
SELECT * FROM employee
ORDER BY RANDOM()
LIMIT 1 OFFSET (SELECT ABS(RANDOM()) % (SELECT COUNT(*) FROM employee));
--OR
--to just skip one record
SELECT * FROM employee
ORDER BY RANDOM()
LIMIT 1 OFFSET 1;

The subquery in the above example calculates a random integer i.e., a random offset value. ABS() function with RANDOM() function generates a random integer and the statement, '% (SELECT COUNT(*) FROM employee' ensures that the generated integer is within the range of total number of rows in the table. We can also give random offset value because when the records are ordered randomly and even if we skip one record, we always get some random record from the query.

Output:

Explanation: Now, we can observe in the above result that the two queries mentioned above return some random records where the first query returns the record with id=5 and the second query returns the record with id=3.

Conclusion

So, SQLite uses SQL language but is a database engine that is based on a relational database management system is serverless, self-contained, and can be used for small mobile or desktop applications. To request a random row or record in SQLite, we can use RANDOM() function along with ORDER BY, LIMIT, and OFFSET clauses. RANDOM() function takes no arguments and returns a random integer. ORDER BY combined with RANDOM() can be used to order the records in the table randomly. After ordering the records, we can use LIMIT and OFFSET clauses to get the desired result or records where OFFSET skips some records and LIMIT describes about the number of records to return.