How to request a random column in SQL?

Question

How can I request a random column (or as close to truly random as is possible) in pure SQL?

I want to select any one column randomly from a table.

I have searched lot regarding this on google.

Answer 1

Dependent on your DBMS the catalogue looks a bit different. For mysql you could get a random column like:

select column_name 
from information_schema.columns 
where table_schema = ? 
  and table_name = ? 
order by rand() limit 1;

and then incorporate that into your query. Other vendors may have a slightly different look of the catalogue, but the idea will be the same.

Why do you want to retrieve a random column?

Answer 2

USE [AdventureWorksDW2012]
GO

DECLARE @SQL VARCHAR(MAX)

SELECT  [name]
FROM    sys.columns C WHERE C.object_id = OBJECT_ID('DimProduct')
AND     C.column_id = (
SELECT  ((ABS(CHECKSUM(NEWID()))% COUNT(*)) + 1)
FROM    sys.columns C 
WHERE   C.object_id = OBJECT_ID('DimProduct'))

This may help you, It is done with SQL Server. Here you are randomly selecting a column of table [DimProduct]

Result1:

Result2:

Answer 3

You can select a random record, but all columns must be referenced by name and not even position.

The only way you can force this is:

1. Get list all column names
2. Randomly select one column name
3. Write the query using the selected column name

Simply there is no way to do it with pie SQL.

Answer 4

In Microsoft SQL, you can achieve this by ordering columns by randomized number (NEWID):

SELECT TOP 1 c.column_name 
FROM   information_schema.columns c 
WHERE  table_name = 'your_table_name' 
ORDER  BY NEWID();