MySQL RAND() Without Repeating a Value in a Specific Column

Question

I'm trying to Select a random set of results (5 results) from a table without selecting two notes from the same user and ordering those results based on date added and having the record added within 30 days. For example, let's say my table has the following columns:

user_notes
  -note_id
  -user_id
  -note
  -note_added

I'm pretty close with

SELECT * FROM
(
    SELECT * FROM user_notes
    WHERE note_added > date_sub(note_added,interval 30 day)
    GROUP BY user_id
    ORDER BY RAND()
) user_notes
ORDER BY note_added DESC LIMIT 0,5

I feel like the group by statement is screwing things up and while a user may have a record added within the last 30 days I'm just getting the first record they create due to the group by. Any suggestions?

sw0x2A · Accepted Answer · 2011-07-27 13:43:23Z

1

I could not test it but for me it looks fine except the date_sub(): Shouldn't it be

date_sub(now(),interval 30 day)?

answered Jul 27, 2011 at 13:43

sw0x2A

3082 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joshua Over a year ago

duh! That was it. Thanks for the help.

SAKTHISARAN · Accepted Answer · 2011-07-27 14:00:32Z

0

Whether you want the random records for a specific user or not? if yes means, you can add one more condition to where clause like Specific user_id. Try it....

answered Jul 27, 2011 at 14:00

SAKTHISARAN

211 bronze badge

1 Comment

Joshua Over a year ago

The question was to do exactly the opposite of that.

Brendan Bullen · Accepted Answer · 2011-07-27 15:37:05Z

0

SELECT * FROM
(
    SELECT * FROM user_notes
    WHERE note_added > date_sub(note_added,interval 30 day)
    ORDER BY RAND()
) user_notes
    GROUP BY user_id
ORDER BY note_added DESC LIMIT 0,5

Try this and take a look at the answer from sw0x2A

edited Jul 27, 2011 at 15:37

Brendan Bullen

11.8k1 gold badge33 silver badges40 bronze badges

answered Jul 27, 2011 at 13:50

Nin

3,0201 gold badge24 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL RAND() Without Repeating a Value in a Specific Column

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related