Timeline for How to request a random row in SQL?
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 1, 2021 at 4:49 | comment | added | mgcation |
@trejder I don't think so. Let's imagine how order by desc limit 1 will work. Full-scan will not be necessary.
|
|
| Feb 12, 2021 at 22:48 | comment | added | user2864740 | @BenPower Without the 'order by NEWID()` the result is not random and doesn't need a sort. This allows the query to return the first results (probably as read by [clustered] index). | |
| Feb 3, 2021 at 2:19 | comment | added | flying_loaf_3 |
For SQLite SQL SELECT column FROM table ORDER BY random() LIMIT xx
|
|
| Oct 2, 2016 at 11:35 | comment | added | Christian Hujer |
These solutions don't scale. They are O(n) with n being the number of records in the table. Imagine you have 1 million records, do you really want to generate 1 million random numbers or unique ids? I'd rather use COUNT() and involve that in a new LIMIT expression with a single random number.
|
|
| Oct 22, 2015 at 22:34 | comment | added | Volodymyr Chumak | For SQLite use RANDOM() function. | |
| Aug 26, 2015 at 23:02 | comment | added | Ben Power | ORDER BY NEWID() seems to be markedly slower on SQL Server. My query looks like: select top 1000 C.CustomerId, CL.LoginName from Customer C inner join LinkedAccount LA on C.CustomerId=LA.CustomerId inner join CustomerLogin CL on C.CustomerId=CL.CustomerId group by C.CustomerId, CL.LoginName having count(*)>1 order by NEWID() Removing the "order by NEWID()" line returns results much faster. | |
| Jun 23, 2015 at 6:42 | comment | added | trejder |
Ten years ago some guy said that using ORDER BY RAND() is wrong...
|
|
| Apr 22, 2015 at 12:57 | vote | accept | sverrejoh | ||
| May 29, 2014 at 21:09 | vote | accept | sverrejoh | ||
| May 29, 2014 at 21:09 | |||||
| May 26, 2014 at 9:27 | comment | added | AD7six |
-1 for relying on order by rand() or equivalents in all dbs :|. also mentioned here.
|
|
| Sep 24, 2013 at 11:27 | vote | accept | sverrejoh | ||
| May 13, 2014 at 13:46 | |||||
| Aug 21, 2013 at 16:39 | history | edited | Yaakov Ellis | CC BY-SA 3.0 |
include content from cited link, to preserve the info in the link no longer works
|
| Aug 21, 2008 at 6:32 | history | answered | Yaakov Ellis | CC BY-SA 2.5 |