patternsqlMajor
Getting a random row from PostgreSQL?
Viewed 0 times
randompostgresqlfromgettingrow
Problem
I want to get a random row from my table by id.
My table:
If my user selects a category and a difficult, I have pick a random word with user parameters, like:
I don't know how to get that random row id (
Note that it must follow the user's selection conditions.
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.idI 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
Since 9.5 there's also the
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.