How select random column from mysql table?

Question

My table is like this -

+---------+---------------+------+-----+---------+-------+
| PK      | ToicID        |topic1|topic2| topic3 |topic4 |
+---------+---------------+------+-----+---------+-------+
| 1       | 1             | abc  | xyz  | mno    | pqr   |
+---------+---------------+------+-----+---------+-------+

I use this query-

select * from table order by rand() limit 1

it's giving me only random row. But I want to select any topic random like following

Topic3
|mno|

==========================================

But conditon is that column should't empty or null.

Possible duplicate of [How to request a random column in SQL?](https://stackoverflow.com/questions/24858797/how-to-request-a-random-column-in-sql) — fvu, Sep 20 '17 at 12:49
See probable duplicate question, but I want to add that the need for a random column indicates a design problem - consider redesigning the table. And don't butcher my edits :) — fvu, Sep 20 '17 at 12:51
@fvu in your reference he is trying to get name from table i want just a random column it maybe topic1,topic3, topic2 any column randomly . — Sunil Kothiyal, Sep 20 '17 at 12:55
Any time you have enumerated columns, alarm bells should start ringing! — Strawberry, Sep 20 '17 at 13:08
Yeah, fix your data model. Anything else is just window dressing. — Strawberry, Sep 20 '17 at 14:20

etsa · Accepted Answer · 2017-09-20T13:49:36.873

3

Pls can you check if this is what you are asking for?

SELECT  CASE FLOOR(RAND()*4+1)
             WHEN 1 THEN TOPIC1 
             WHEN 2 THEN TOPIC2 
             WHEN 3 THEN TOPIC3 
             WHEN 4 THEN TOPIC4 END AS TOPIC_RAND
FROM TABLE;

Updated version after comments and added clause -NULL/blank in question:

   select version() as 'mysql version';

SELECT  CASE FLOOR(RAND()*4+1)   /*wrote here just 1 to make tests */
    WHEN 1 THEN COALESCE(TOPIC1, TOPIC2, TOPIC3, TOPIC4)  
    WHEN 2 THEN COALESCE(TOPIC2, TOPIC3, TOPIC4, TOPIC1) 
    WHEN 3 THEN COALESCE(TOPIC3, TOPIC4, TOPIC1, TOPIC2) 
    WHEN 4 THEN COALESCE(TOPIC4, TOPIC1, TOPIC2, TOPIC3) END AS TOPIC_RAND
FROM (SELECT NULLIF(TOPIC1,'') AS TOPIC1, NULLIF(TOPIC2,'') AS TOPIC2,NULLIF(TOPIC3,'') AS TOPIC3,NULLIF(TOPIC4,'') AS TOPIC4          
      FROM TTE1) A;

Sample data

CREATE TABLE TTE1 (TOPIC1 VARCHAR(10), TOPIC2 VARCHAR(10), TOPIC3 VARCHAR(10), TOPIC4 VARCHAR(10));
INSERT INTO TTE1 VALUES ('abc','cde','efg','hij');
INSERT INTO TTE1 VALUES (NULL,'k2','k3','k4');
INSERT INTO TTE1 VALUES ('','i2','i3','i4');

Output:

mysql version
1   5.7.12-log

TOPIC_RAND
1   cde
2   k4
3   i2

edited Sep 20 '17 at 13:49

answered Sep 20 '17 at 12:54

etsa

5,020
1
7
18

I don't need if the column is blank or null, it should go to next one which has data. how will it do? – Sunil Kothiyal Sep 20 '17 at 13:03
@Sunny See COALESCE in the mean time... Try to find solution by yourself... In a few minutes I'll update answer. Let me know if you find solution by yourself – etsa Sep 20 '17 at 13:05
@ esta "Unexpected token" and "Unrecognized keyword." are errors. – Sunil Kothiyal Sep 20 '17 at 13:18
I am working on rextester... Now I am posting output too for you. Check if you wrote anything correctly... – etsa Sep 20 '17 at 13:20
Hi @etsa thank you for your answer, My mysql version is 5.1.73-community. Is this reason for showing errors?? – Sunil Kothiyal Sep 20 '17 at 13:30
Try single things to see where you got error: eg. try SELECT CASE WHEN 1=1 THEN 'One' ELSE 'Two' END, but I still think you misspelled something... – etsa Sep 20 '17 at 13:41

score 1 · Answer 2 · answered Sep 20 '17 at 16:40

1

A normalized schema might look like this (snippet):

topic_id topic_name 
       1 abc  
       2 xyz  
       3 mno  
       4 pqr

From here, the problem becomes trivial.

answered Sep 20 '17 at 16:40

Strawberry

33,750
13
40
57

How select random column from mysql table?

2 Answers2