Order by random and LIKE

Question

I want to get random row from database, but limit it using some content.

For example, table's definition:

id | content | tags

The query is: SELECT * FROM tbl ORDER BY RAND() LIMIT 1.

Tags part looks like: tag1, tag2, tag42. So, for example, I want one random row from ones which have tag42 or tag2 inside tags.

Some pseudocode:

SELECT * FROM tbl ORDER BY RAND() WHERE tags LIKE '%tag42%' OR '%tags2%' LIMIT 1.

Your ORDER BY and WHERE are the wrong way round? – Jack Pettinger Mar 11 '13 at 19:08 — Jack Pettinger, Mar 11 '13 at 19:08

score 3 · Accepted Answer · answered Mar 11 '13 at 19:08

3

Your pseudocode is almost correct, except you have order of clauses wrong: WHERE comes before ORDER BY

SELECT * FROM tbl WHERE tags LIKE '%tag42%' OR '%tags2%' ORDER BY RAND() LIMIT 1

See the documentation for SELECT syntax, to never get wrong again

answered Mar 11 '13 at 19:08

Mchl

It would take much time on a big table. – ravnur Mar 11 '13 at 19:45
Yes it would. But hey, he's keeping tags as a comma delimited string! – Mchl Mar 11 '13 at 20:01
1

Yeah, the result isn't so big and there are less than 200 rows in db. That's why the tags are stored as a string, etc. – Max Frai Mar 11 '13 at 21:49

1 Answers1