Solved

ASP.NET  4 random rows from a query

Posted on 2004-03-23
13
972 Views
Last Modified: 2006-11-17
I'm looking for the easiest way to grab 4 random rows from an SQL query.   I guess I need to know how to extract individual rows from the dataset based on row index or something.
0
Comment
Question by:drew22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 12

Expert Comment

by:dfiala13
ID: 10662593
How many records are you pulling from?  Might be worth doing in SQL and only returning 4 random rows.
0
 

Expert Comment

by:mkanik
ID: 10662646
you can use that code as a reference

<% Option Explicit %>

<!-- #include virtual="/adovbs.inc"--> <%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER=SQL Server;SERVER=OFFICEDNS;" & _
                           "UID=SSDB;PWD=;DATABASE=SSDB;"
objConn.Open
      
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM tblItem WHERE ParentID IS NULL", objConn, _
           adOpenStatic, adLockReadOnly

Dim rndMax
rndMax = CInt(objRS.RecordCount)
objRS.MoveFirst
      
Do While Not objRS.EOF
      Response.Write objRS("ID") & "<BR>"
      objRS.MoveNext
LOOP
      
objRS.MoveFirst
      
Dim rndNumber
Randomize Timer
rndNumber = Int(RND * rndMax)
      
objRS.Move rndNumber

Response.Write objRS("ID")


objRS.Close
Set objRS = Nothing
      
objConn.Close
Set objConn = Nothing

%>  

0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10662709
CREATE TABLE #ROWID(
 DummyID int IDENTITY,
 RealID int)

CREATE TABLE #RandomIDs(
 RealID int)

DECLARE @Total int
DECLARE @i int
DECLARE @iRandomID int
INSERT INTO #ROWID (RealID) SELECT MyID FROM MyTable
SELECT @Total = @@ROWCOUNT


SET @i = 1
WHILE (@i <= 4)
BEGIN
  SET @iRandomID = Convert(int, RAND()*@Total)
  INSERT INTO #RandomIDs(RealID) SELECT FROM #ROWID Where DummyID = @iRandomID

END

SELECT m.* FROM MyTable m Inner Join #RandomIDs r on r.RealID = m.myID
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:drew22
ID: 10662769
I don't know how to select 4 random rows using SQL on Oracle.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10663385
0
 
LVL 1

Author Comment

by:drew22
ID: 10663589
sample() doesn't work with multiple tables:
"ORA-30561: SAMPLE option not allowed in statement with multiple table references"


I got a "ORA-00920: invalid relational operator" from this example:
select empno,ename
from ( select * from emp where deptno = 20 order by dbms_random.random)
where rownum <= 3

What about just grabbing 4 random rows from an ASP.NET dataset?
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10664107
Just means you have to move more data across the wire than you need, but still doable.

This assumes rows you want are in table 0 of the dataset...

DataTable dt =  ds.Tables[0];
int iTotal =dt.Rows.Count;
int iRandom = 0;
DataTable dtRandom = dt.Clone();
DataRow dr = null;
for(int i = 0; i < 4; i++){
   iRandom = (new Random().Next(iTotal));
  dr = dt.Rows[iRandom];
  dtRandom.Rows.Add(dr.ItemArray);

}
dg.DataSource = dtRandom;
0
 
LVL 1

Author Comment

by:drew22
ID: 10670513
dfiala13,

Is there a possibility that with small datasets you get duplicate random rows?  If the dataset contains 20 rows and you're grabbing 4 randoms,  could you get dupes?
0
 
LVL 12

Accepted Solution

by:
dfiala13 earned 500 total points
ID: 10670843
Sure,
You could, even in a large dataset.  You can solve this by adding a hashtable and adding an extra step:

DataTable dt =  ds.Tables[0];
int iTotal =dt.Rows.Count;
Hashtable ht = new Hashtable();
int iRandom = 0;
DataTable dtRandom = dt.Clone();
DataRow dr = null;
while(ht.Count < 4){
   iRandom = (new Random().Next(iTotal));
   if(!ht.ContainsValue(iRandom))
     ht.Add(i, iRandom);

}
for(int i = 0; i < ht.Count; i++){
  dr = dt.Rows[(int)ht[i]];
  dtRandom.Rows.Add(dr.ItemArray);
}
dg.DataSource = dtRandom;
0
 
LVL 1

Author Comment

by:drew22
ID: 10671501
dfiala,


this part was missing the i var incrementer for ht.Add(), but I added it and it worked.

while(ht.Count < 4){
   iRandom = (new Random().Next(iTotal));
   if(!ht.ContainsValue(iRandom))
     ht.Add(i, iRandom);

}





0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10671552
oops.  sorry, should no better than to write code in these boxes.  Glad you got it to work.

If you start needing to pull from more than a few hundred records it would definitely be worth the time to figure out how to make Oracle do the dirty work for you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question