Created
December 19, 2012 03:10
-
-
Save bdargan/4334059 to your computer and use it in GitHub Desktop.
select random rows from various databases
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax. | |
Select a random row with MySQL: | |
SELECT column FROM table | |
ORDER BY RAND() | |
LIMIT 1 | |
Select a random row with PostgreSQL: | |
SELECT column FROM table | |
ORDER BY RANDOM() | |
LIMIT 1 | |
Select a random row with Microsoft SQL Server: | |
SELECT TOP 1 column FROM table | |
ORDER BY NEWID() | |
Select a random row with IBM DB2 | |
SELECT column, RAND() as IDX | |
FROM table | |
ORDER BY IDX FETCH FIRST 1 ROWS ONLY | |
Thanks Tim | |
Select a random record with Oracle: | |
SELECT column FROM | |
( SELECT column FROM table | |
ORDER BY dbms_random.value ) | |
WHERE rownum = 1 | |
Thanks Mark Murphy | |
Feel free to post other example, variations, and SQL statements for other database servers in the comments. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment