Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to pull out rows from a postgres database, I can pull them out desc but when I try random I get a Syntax error near random.

Error

PG::Error: ERROR:  syntax error at or near "rand"
LINE 1: ...  "hashtags".* FROM "hashtags"  ORDER BY tweet_id rand LIMIT...
: SELECT  "hashtags".* FROM "hashtags"  ORDER BY tweet_id rand LIMIT 4
Code to pull it out
<div id="hashtags">
<% Hashtag.order("tweet_id desc").limit(4).each do |hashtag| %>
   <blockquote><%= hashtag.content %></blockquote>
   <div class="from">&mdash; @<%= hashtag.screen_name %></div>
 <% end %>
                Er, your code example isn't using random(); it's using desc. Is that the right code? Also, as an aside, your query should go in your controller, not your view. Finally, I suggest testing stuff like this in the Rails console.
– user24359
                Nov 30, 2012 at 7:59
                just put in @random_hashtags = Hashtag.order(...).limit(4), and then just refer to it in your view like @random_hashtags.each do....
– user24359
                Nov 30, 2012 at 8:26
To fetch random entries from your database you have a few options. Here's a couple
1st approach
This will take 4 random entries out of your DB using SQL.
Hashtag.order("RANDOM()").limit(4)
2nd approach:
You can also use ActiveRecord sample() method to retrieve 4 random rows.
Hashtag.all.sample(4)
As of speed and efficiency; I made a mini-benchmark and tested two commands on my own db (contains 500 records).
The first approach (as expected) was more than twice faster than the second approach.
SQL: 1.8ms
Sample Method: 4.2ms
                Actually I know why that doesn't work and why I was trying to do   <% Hashtag.order("tweet_id rand").limit(4).each do |hashtag| %>  Was so it would only pull out random rows from tweet_id matched a certain value
– thebusiness11
                Nov 30, 2012 at 8:14
                The second approach is slower because it's the Ruby code that picks the random and not SQL. It gets every row in hashtags table and randomly select 4, never use it.
– Jirico
                Mar 5, 2013 at 20:45
                you might realize this by now, being it's a few years later, but this isn't optimal. You're essentially loading all the records and then shuffling once they're all in memory. If you need 5 random records out of a million, your method would require loading the entire million. Just wanted  to put that out there. I dont know a better solution yet.
– max pleaner
                Dec 15, 2015 at 1:23
                The shuffle way wasn't optimal but for a smaller website i was working on i did find this way useful :)
– Sharn White
                Jul 10, 2018 at 1:20
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.