Select random value in SQL

Question

Is there a way to do the following query? It would save me having to go out of SQL to do post-processing of the query:

SELECT date, RANDOM('CA', 'DE', 'AZ') FROM table LIMIT 3

And the result would be something like:

2014-01-01,"CA"
2014-01-02,"CA"
2014-01-03,"DE"

Is there a way to do the RANDOM operation in SQL?

Answer 1

Get your set of values to table/subquery with UNION ALL, sort by RAND() and return 1 value:

SqlFiddleDemo

SELECT id,
       (SELECT 'CA' AS 'col'
        UNION ALL
        SELECT 'DE'
        UNION ALL
        SELECT'CZ'
        ORDER BY RAND() LIMIT 1) AS Random
FROM tab
LIMIT 3

Or use:

SqlFiddleDemo_2

SELECT id,
      ELT(FIELD(CEILING(RAND()*3), 1, 2, 3),'CA','CZ', 'DE') AS Rand
FROM tab
LIMIT 3

Answer 2

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('CA,DE,AZ',',',CEILING(RAND()*3)),',',-1);

or something like that

Answer 3

One way is this if you want to select the random string

select any_column_name,ELT(FLOOR(RAND()*8)+1,'US', 'CA', 'FR', 'DE' , 'UK' , 'IR' , 
  'RU' , 'GR') from table_name;

Let me know if that is what you want.