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?

is there is any problem if you get the info to array and take random one from this array ? — badr aldeen
– badr aldeen, Commented Sep 7, 2015 at 16:49

Lukasz Szozda · Accepted Answer · 2015-09-07 17:08:10Z

2

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

edited Sep 7, 2015 at 17:08

answered Sep 7, 2015 at 16:56

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Lukasz Szozda Over a year ago

@David542 If there is a lot of values instead of UNION ALL use predefined table.

Strawberry · Accepted Answer · 2015-09-07 16:59:27Z

1

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

or something like that

answered Sep 7, 2015 at 16:59

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

Domain · Accepted Answer · 2015-09-19 13:08:33Z

0

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.

edited Sep 19, 2015 at 13:08

answered Sep 7, 2015 at 17:03

Domain

11.8k3 gold badges26 silver badges45 bronze badges

Collectives™ on Stack Overflow

Select random value in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related