How to return 1 RANDOM record

[Shrum]

I'm trying to create a random record SQL statement but do not:

1) Know if it is possible (I'm assuming that it does)
2) Know the correct syntax to pull it off.

All I want is 1 record at random. I am creating a media archive so the thought here is that each time the user refreshes a page, a random image can be displayed.

One point of order that must be made here is that the DBMS that I am running this on is self-created using PERL and DBI and SQL::Statement. While SQL::Statement is quite robust, it may not support function calls in the SQL query itself.

Any help would be apprechiated.

TIA

Sean Shrum

 

[r937]

does your table have a primary key? how are the values of the primary key distributed?

rudy

 

[bkj123]

Shrum

Have you ever used the random keyword in a where clause:
where random(1,10) = 1

In this example, an integer with a value of 1 to 10 is placed next to each record. The where statement returns all records with a value of 1. So, you'll get a random sample of 10% in this case.

If your table will always have the same number of records then you can do something like "where random(1,N)=1" and 'N' is the number of records.

If you do not know the number of records or they may change, try a subquery...

select *
from table_x
where random(1,(select count(*) from table_x))=1;

See what you think. - bkj123

 

[JamesLean]

In SQL Server you can use the NEWID() function to generate a random ordering key and just select the first record using TOP:

SELECT TOP 1 *
FROM mytable
ORDER BY NEWID()

I don't know whether that would be supported in your DBMS?

--James

 

[dnoeth]

And in Teradata it's just:
select * from table sample 1;

So there's no easy way using Standard SQL before SQL:2003, which will include a <sample clause>

Dieter

 

[Shrum]

Thanks for all the responses...and Im sorry for my late response (on-going issues with Globat over an email I sent to their users).

Unfortunately, I found out from the programmer of the SQL::Statement module that the RAND() is not implemented. However, in a future release, this will be.

So until then I have to wait.

Thanks again.

Sean Shrum

 

[rwheels]

It's by no means fool proof, and you could argue it's hardly random. However, if there is a character field longer than 60 characters in your table, you could do something like this.


select top 1 *
from your_table
order by substring(field1,datepart(s,getdate()),10)

This orders the table using the value of the second that the refresh occured, and retrieves a string of 10 letters beggining as many characters in as the second that it grabbed.