Home » SQL & PL/SQL » SQL & PL/SQL » Selecting a random record from table
Selecting a random record from table [message #292256] Tue, 08 January 2008 05:18 Go to next message
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 #292259 is a reply to message #292256] Tue, 08 January 2008 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would
SELECT * FROM your_table WHERE rownum = 1
be good enough? Or, a little bit more sophisticated,
SELECT * FROM (SELECT * FROM your_table
               ORDER BY DBMS_RANDOM.VALUE
              )
WHERE rownum = 1;

[Updated on: Tue, 08 January 2008 05:26]

Report message to a moderator

Re: Selecting a random record from table [message #292260 is a reply to message #292256] Tue, 08 January 2008 05:30 Go to previous messageGo to next message
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 #292261 is a reply to message #292256] Tue, 08 January 2008 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select * from mytable sample block (1) where rownum = 1;

Regards
Michel
Re: Selecting a random record from table [message #292262 is a reply to message #292259] Tue, 08 January 2008 05:31 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Littlefoot wrote on Tue, 08 January 2008 11:26

Would
SELECT * FROM your_table WHERE rownum = 1
be good enough? [/code]

I was going to add that in, but I thought it might end up opening a whole can of 'But what if's' Smile
Re: Selecting a random record from table [message #292264 is a reply to message #292256] Tue, 08 January 2008 05:37 Go to previous messageGo to next message
javon
Messages: 7
Registered: December 2007
Junior Member
Hi.

Thanks to all of you for the quick answers! I just returned from a coffee break and wow!

Sorry for the mess - I thought this was a difficult question!
I'll do my homework next time.

Thanks again!

Re: Selecting a random record from table [message #292266 is a reply to message #292260] Tue, 08 January 2008 05:52 Go to previous messageGo to next message
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 #292268 is a reply to message #292266] Tue, 08 January 2008 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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
Re: Selecting a random record from table [message #292270 is a reply to message #292268] Tue, 08 January 2008 06:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #292564 is a reply to message #292353] Wed, 09 January 2008 03:58 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Good point, Bill! I was about to post about SAMPLE. Nice to see others have the same ideas.

MHE
Re: Selecting a random record from table [message #292595 is a reply to message #292353] Wed, 09 January 2008 05:38 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
SQL> select *
  2  from (select * from employees sample(1))
  3  where rownum = 1;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID          X
-------------- ---------- ------------- ----------
        116 Shelli               Baida
SBAIDA                    515.127.4563         24-DEC-97 PU_CLERK         2900
                      114            30


SQL> /

no rows selected

Doesn't seem to be the case. (More likely, I'm missing something obvious Smile )
Re: Selecting a random record from table [message #292606 is a reply to message #292595] Wed, 09 January 2008 06:11 Go to previous messageGo to next message
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 Smile )


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #292638 is a reply to message #292632] Wed, 09 January 2008 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So it seems percentage is not really better than block.
After all, it should depend on your table space allocation.
Once the table starts to be filled, that is when you get close to HWM in MSSM, or you have at least about n*number of blocks in ASSM, it is less likely that block will return no row.

Regards
Michel
Re: Selecting a random record from table [message #292645 is a reply to message #292638] Wed, 09 January 2008 09:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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 Wink )

[Updated on: Fri, 25 September 2009 02:57]

Report message to a moderator

Previous Topic: Pivoting or Transposing
Next Topic: Mutating Trigger Error
Goto Forum:
  


Current Time: Mon May 19 06:11:18 CDT 2025