CODEWITHSUNDEEP
sqlrandom
Prikshit
Prikshit

Reputation: 304

How to request a random column in SQL?

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.

Upvotes: 5

Views: 4447

Answers (4)

Dariusz Woźniak
Dariusz Woźniak

Reputation: 10350

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();

Upvotes: 0

Jithin Shaji
Jithin Shaji

Reputation: 6073

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:

enter image description here

Result2:

enter image description here

Upvotes: 1

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

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?

Upvotes: 1

denisvm
denisvm

Reputation: 740

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.

Upvotes: 0

Related Questions