Selecting a random record from table [message #292256] |
Tue, 08 January 2008 05:18  |
javon
Messages: 7 Registered: December 2007
|
Junior Member |
|
|
Hi.
Can anybody tell me what should be the SQL to select a random record from a table?
Of course, this should be generic enough to be applied on any table.
Thanks in adavnce.
|
|
|
|
Re: Selecting a random record from table [message #292260 is a reply to message #292256] |
Tue, 08 January 2008 05:30   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Hi, as you are a first time poster, I will be happy to post a solution (trying to turn over a new leaf in the new year) However, this question has been asked many, many times in these forums and power(many, many) times on the web. Please try doing some basic searches before asking these questions. In addition, this is a long way from being an Expert question and should have been posted in the newbies forum (I'm sure that one of the mods will remedy this soon)
Anyway, you can use dbms_random in the order by clause to get this
Here is a simple example:
select *
from( select *
from employees
order by dbms_random.value
)
where rownum =1
|
|
|
|
|
|
Re: Selecting a random record from table [message #292266 is a reply to message #292260] |
Tue, 08 January 2008 05:52   |
javon
Messages: 7 Registered: December 2007
|
Junior Member |
|
|
Hi pablolee/Littlefoot.
I've tried your query and it works nicely.
from( select *
from employees
order by dbms_random.value
)
where rownum =1
One question though:
The query seems to have potential performance problem, as the inner SELECT sorts the table, probably not on a key field.
If the above is true - is there a solution for this?
|
|
|
|
Re: Selecting a random record from table [message #292270 is a reply to message #292268] |
Tue, 08 January 2008 06:28   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 08 January 2008 13:11 | This is why I gave my solution which is not perfect as you can sometimes get an empty block and so no row.
Regards
Michel
|
I think using the sample method is the preferred way of doing it. If you would run into the above mentioned problem (too) often, you can always choose to increase the sample size.
|
|
|
Re: Selecting a random record from table [message #292353 is a reply to message #292256] |
Tue, 08 January 2008 12:36   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Instead of using "sample block", use
select *
from (select * from mytable sample(1))
where rownum = 1;
Which will return a random select of 1% of the table, which will then be restricted to the first row in that random selection. By avoiding blocks, you will always get a row returned.
|
|
|
|
|
Re: Selecting a random record from table [message #292606 is a reply to message #292595] |
Wed, 09 January 2008 06:11   |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
pablolee wrote on Wed, 09 January 2008 | Doesn't seem to be the case. (More likely, I'm missing something obvious )
|
Just have a few retries, it'll work.
I've just done a quick test myself:
SELECT * FROM EMP SAMPLE(1)
And once in a while it returned No rows selected.
[Updated on: Wed, 09 January 2008 06:19] by Moderator Report message to a moderator
|
|
|
Re: Selecting a random record from table [message #292631 is a reply to message #292606] |
Wed, 09 January 2008 08:28   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote: | Just have a few retries, it'll work.
| My point was that
Quote: | By avoiding blocks, you will always get a row returned.
| Does not seem to be true. i.e. sometimes no row will be returned (as, seemingly, confirmed by yourself too).
Quote: | And once in a while it returned No rows selected.
|
I was not refuting that it will return 1 row, I was refuting that it will always return 1 row (unless, as I say, I have missed something.
|
|
|
Re: Selecting a random record from table [message #292632 is a reply to message #292631] |
Wed, 09 January 2008 08:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This was my point when I advised to raise the percentage
The percentage is an approximate; it will return approx. n% rows.
If your main dataset is small, it can result in no records present in the resultset.
|
|
|
|
Re: Selecting a random record from table [message #292645 is a reply to message #292638] |
Wed, 09 January 2008 09:00   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Depends on blocksize vs recordsize. If the ratio between the two is close to one, chances are that a (relatively) large percentage of the blocks would contain no records. That would mean that even though you get multiple blocks, you still have no records.
|
|
|
Re: Selecting a random record from table [message #423519 is a reply to message #292256] |
Fri, 25 September 2009 00:35   |
april_123456
Messages: 1 Registered: September 2009
|
Junior Member |
|
|
There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.
Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
|
|
|
Re: Selecting a random record from table [message #423535 is a reply to message #423519] |
Fri, 25 September 2009 02:50   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It's been 18 months since anyone else posted to this thread.
Why did you feel the need to repeat the method that @Littlefoot posted in the first reply, while adding ways of doing it that people can't use because they'er for non-oracle databases?
|
|
|
Re: Selecting a random record from table [message #423536 is a reply to message #423519] |
Fri, 25 September 2009 02:56  |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
*Sigh*
1. Why post Non-Oracle syntax? This is an Oracle site, our answers are based on that.
2. This thread is nearly 2 years old, you haven't really added anything (and in fact you have used the same Oracle 'solution' as has been posted twice already in the thread, so why even bother?
Beaten to it (21 Months by my reckoning JRowbottom )
[Updated on: Fri, 25 September 2009 02:57] Report message to a moderator
|
|
|