Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
So you want a random row (from the many) that fit the Difficult = 'Easy' AND Category_id = 3 ? –  ypercube Oct 14 '12 at 23:23
    
Excacly, how can I do this? –  Bernardo Vale Oct 15 '12 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. –  Erwin Brandstetter Oct 15 '12 at 22:20

1 Answer 1

up vote 1 down vote accepted

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;
share|improve this answer
    
Work fine, thanks xD –  Bernardo Vale Oct 15 '12 at 0:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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