Random Record Selection
-
Hi,
My table has around 750000 records.I need to retrieve 5 random records at each and every execution.
Anyone help me to resolve this issue.
Regards
Karthik
karthik
-
Here is what I use to pull random records.
begin
declare @maxcount int,@winner int
declare @winnerid int
-- get number of entries and choose a random number
set @maxcount = (select count(*) from sometable)
if @maxcount > 0
begin
set @winner = (select convert(int,(@maxcount * rand())+1))
-- pull the winner out of the hat
declare hat scroll cursor for select uid from sometable
open hat
fetch absolute @winner from hat into @winnerid
close hat
deallocate hat
select @winnerid
-
not very performant but you could try:
select top 5 *
from [your table] with (nolock)
order by newid()
* Noel -
Neold,
is it necessary to give nolock ? or shall i give table name only ?
will NEWID() affect performance ? if the table has lot of records.
Here i have one basic question , what is the difference between NEWID() and RAND() ?
Regards
Karthik
karthik
-
Karthik,
The problem with what I posted is that regardless is a full table scan. Nolock will minimize the impact on others by not creating a shared lock in the entire table.
It maybe more resonable to compute something based on your "primary key" and then select those records but then again I have no clue about the definitions of your table.
The difference with rand() is that rand() is exceuted *once* per query and newid() is per record. I just gave you a quick and dirty way of doing it but it is definitely not the most performant.
By the way
did not came out right beacuse I used "<<<" instead of [[[ it is fixed now.
Cheers,
* Noel -
Nelod, Thanks for your answer.
Is the NEWID() function used with ORDERBY clause only ? Can you explain me the reason for using ORDERBY in NEWID() Function ?
*Karthik
karthik
-
NEWID() is used to general GUID values which are supposed to be globally unique values.
It is normally used in tables that Identify records using such values.
The use of it on the ORDER BY clause is just a "hack" but it works
For more read BOL.
* Noel -
To see what's the difference between NEWID() and RAND() try this:
SELECT NEWID(), RAND(), SomeColumn
FROM SomeTable
SomeTable must have more than 1 row.
_____________
Code for TallyGenerator -
You running a 5 digit lottery?
Seriously, though, just because I'm curious everytime I see such a requirement... what is the business logic that created this requirement? In other words, why do you need to do this?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sounds almost like a homework assignment, doesn't it?
ButI use it in my company to give auditors random examples of customers accounts.
-
Thanks, Chris... appreciate that...
Karthik... how about you... what are you going to use this for? The "select random rows" problem is pretty rare in SQL and I'm still curious what you're going to use this for.
As a matter of fact, if anyone has been required to produce random rows, I wouldn't mind hearing from you, either. Thanks
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have used it in a Call Center application. The company just wouldn't accept the possibility that an agent phoned a lead on the same street twice or more sequentially.
The lead lists were bought externally and could be in any sort order. Some times by last name, some times by phone number and some times by street address.
N 56°04'39.16"
E 12°55'05.25" -
"As a matter of fact, if anyone has been required to produce random rows, I wouldn't mind hearing from you, either."
I once wrote a query to return 20 Random telephone numbers. I then ran it 100 times to generate test scripts. At the time I was working in telecoms, and we wanted to test new functionality in a UAT environment by getting 100 people to stay an hour over and use the application to query the db.
A fairly large multi-user test was essential to ensure the app would run for its intended target of 600 users.
Dave J
-
Thanks for the feedback, one and all... funny how the telcom business seems to need it the most
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply