16

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.

3
  • So you want a random row (from the many) that fit the Difficult = 'Easy' AND Category_id = 3 ? Oct 14, 2012 at 23:23
  • Excacly, how can I do this? Oct 15, 2012 at 0:00
  • 1
    Closely related question on SO. ORDER BY random() LIMIT 1 is actually rather slow because it needs to scan and sort the whole table. Fine for small tables, impractical for big tables. Oct 15, 2012 at 22:20

1 Answer 1

15

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.

2
  • 2
    This way is very slow
    – Minions
    May 20, 2020 at 10:13
  • Yes, it is. This isn't a use case SQL was really specified for. However newer PostgreSQL versions have tablesample support now. May 21, 2020 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.