{"id":8637,"date":"2020-05-19T10:13:22","date_gmt":"2020-05-19T00:13:22","guid":{"rendered":"https:\/\/database.guide\/?p=8637"},"modified":"2020-06-28T09:40:13","modified_gmt":"2020-06-27T23:40:13","slug":"how-sqlite-random-works","status":"publish","type":"post","link":"https:\/\/database.guide\/how-sqlite-random-works\/","title":{"rendered":"How SQLite Random() Works"},"content":{"rendered":"\n

The SQLite<\/a> random()<\/code> function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.<\/p>\n\n\n\n

A pseudo-random number is a number that appears to be random, but is not truely random. A pseudo-random number is not truely random because its value was generated by a known seed. However, a pseudo-random number will appear to be random if the user has no knowledge of the seed or algorithm that created it.<\/p>\n\n\n\n

Therefore, pseudo-random numbers are often considered good enough for many applications.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s an example of generating a pseudo-random number with the random()<\/code> function.<\/p>\n\n\n\n

SELECT random();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
-882536775989953141<\/pre>\n\n\n\n
Positive Values Only<\/h2>\n\n\n\n
You can combine random()<\/code> with abs()<\/code> to generate only positive values.<\/p>\n\n\n\n
SELECT abs(random());<\/code><\/pre>\n\n\n\n
In this case, if random()<\/code> generates a negative value, abs()<\/code> will return the absolute value instead.<\/p>\n\n\n\n
To demonstrate how abs()<\/code> works, here’s what happens if I pass the (negative) random number from the first example to the abs()<\/code> function:<\/p>\n\n\n\n
SELECT abs(-882536775989953141);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
882536775989953141<\/pre>\n\n\n\n
So if we pass random()<\/code> to abs()<\/code>, and random()<\/code> generates a negative value, abs()<\/code> will return that value as a positive value.<\/p>\n\n\n\n
Value Between 0 and 100<\/h2>\n\n\n\n
Here’s an example of generating a positive number between 0 and 100.<\/p>\n\n\n\n
SELECT abs(random() % 100);<\/code><\/pre>\n\n\n\n
Here’s an example of selecting multiple random values.<\/p>\n\n\n\n
SELECT \n  abs(random() % 100) AS R1,\n  abs(random() % 100) AS R2,\n  abs(random() % 100) AS R3;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
R1          R2          R3        \n----------  ----------  ----------\n17          79          90        <\/pre>\n\n\n\n
Return Random Rows<\/h2>\n\n\n\n
You can use random()<\/code> in an ORDER BY<\/code> clause of a database query to return random rows.<\/p>\n\n\n\n
Here’s an example.<\/p>\n\n\n\n
SELECT * FROM Artist \nORDER BY random() LIMIT 5;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ArtistId    Name                                    \n----------  ----------------------------------------\n131         Smashing Pumpkins                       \n127         Red Hot Chili Peppers                   \n169         Black Eyed Peas                         \n60          Santana Feat. Dave Matthews             \n240         Gustav Mahler                           <\/pre>\n\n\n\n
And here’s what I get if I run it again:<\/p>\n\n\n\n
ArtistId    Name                                    \n----------  ----------------------------------------\n234         Orchestra of The Age of Enlightenment   \n126         Raul Seixas                             \n76          Creedence Clearwater Revival            \n182         Nega Gizza                              \n63          Santana Feat. Lauryn Hill & Cee-Lo      <\/pre>\n\n\n\n
If you have a large table, you might want to modify your query to something like this:<\/p>\n\n\n\n
SELECT * FROM Artist \nWHERE ArtistId IN \n  (SELECT ArtistId FROM Artist ORDER BY random() LIMIT 5);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ArtistId    Name                                    \n----------  ----------------------------------------\n45          Sandra De S\u00e1                            \n105         Men At Work                             \n110         Nirvana                                 \n205         Chris Cornell                           \n267         G\u00f6teborgs Symfoniker & Neeme J\u00e4rvi     <\/pre>\n","protected":false},"excerpt":{"rendered":"
The SQLite random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. A pseudo-random number is a number that appears to be random, but is not truely random. A pseudo-random number is not truely random because its value was generated by a known seed. However, a pseudo-random number will appear to be random if the … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100],"tags":[93],"class_list":["post-8637","post","type-post","status-publish","format-standard","hentry","category-sqlite","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/8637","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=8637"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/8637\/revisions"}],"predecessor-version":[{"id":9345,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/8637\/revisions\/9345"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=8637"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=8637"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=8637"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}