HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Getting a random row from PostgreSQL?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
randompostgresqlfromgettingrow

Problem

I want to get a random row from my table by id.

My table:

ID|Word     |Dificult|Category_id|
1 |'Dumb'   |'Easy'  | 3         |
2 |'Leopard'|'Medium'| 6         |


If my user selects a category and a difficult, I have pick a random word with user parameters, like:

idRaffle := raffle.id;
   -- raffle.id = An id that postgres will bring me by some raffle function. 
d := 'Easy';
c := 3;
select * from words where id=idRaffle and Dificult=d and Category_id=c

raffle.id


I don't know how to get that random row id (raffle.id).

Note that it must follow the user's selection conditions.

Solution

To pick a random row, see:

quick random row selection in Postgres

SELECT *
FROM words
WHERE Difficult = 'Easy' AND Category_id = 3
ORDER BY random()
LIMIT 1;


Since 9.5 there's also the TABLESAMPLE option; see documentation for SELECT for details on TABLESAMPLE.

Code Snippets

SELECT *
FROM words
WHERE Difficult = 'Easy' AND Category_id = 3
ORDER BY random()
LIMIT 1;

Context

StackExchange Database Administrators Q#26929, answer score: 20

Revisions (0)

No revisions yet.