ASP.NET 4 random rows from a query

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.
LVL 1
drew22Asked:
Who is Participating?
 
dfiala13Connect With a Mentor Commented:
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
 
dfiala13Commented:
How many records are you pulling from?  Might be worth doing in SQL and only returning 4 random rows.
0
 
mkanikCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
dfiala13Commented:
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
 
drew22Author Commented:
I don't know how to select 4 random rows using SQL on Oracle.
0
 
drew22Author Commented:
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
 
dfiala13Commented:
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
 
drew22Author Commented:
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
 
drew22Author Commented:
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
 
dfiala13Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.