Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select 3 random rows from dataset in ASP

Posted on 2007-11-20
5
Medium Priority
?
1,082 Views
Last Modified: 2008-03-08
Hi,

I have the following asp script that reads from a ms sql database.
It currently returns the data for all rows.
Can somebody please modify this to return 3 different RANDOM rows instead of all data in the loop attached.

I've tried to modify and can't get it to work. Any help apreciated.
<html>
<!--#INCLUDE Virtual="/asp/user/dbHelpers.asp" -->
<%
 
sql = "SELECT listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"
 
If listingType <> "" Then
	sql = sql & " AND EntryType =  '" & listingType & "'"
End If
 
if countryCode <> "" Then
	sql = sql & " AND country = '" & countryCode & "'"
End If
 
If state <> "" Then
	sql = sql & " AND state = '" & state & "'"
end if
 
sql = sql & " ORDER BY isnull(payedmembersince, '10/10/2100') ASC, CASE Country WHEN '' THEN 'ZZZZ' ELSE Country END ASC, CASE State WHEN '' THEN 'ZZZZ' ELSE State END ASC, CASE City WHEN '' THEN 'ZZZZ' ELSE City END ASC, FullName ASC, CompanySchoolGuild ASC"
 
' default is guild
title = "Hypnosis Guild &amp; Associations Listing"
 
fields = Array(Array("FullName", "Full Name"), Array("CompanySchoolGuild", "Company Name"), Array("City", "City"), Array("State", "State"), Array("Country", "Country"))
 
If listingType = "therapist" Then
	title = "Hypnotist &amp; Hypnotherapist Listing in the "
	If countryCode = "" Then
		title = title & "World"
	Else
		title = title & "United States"
	End If
ElseIf listingType = "school" Then
	title = "Hypnosis Schools &amp; Training Listing"
ElseIf listingType = "stage" Then
	title = "Stage Hypnotists Listing"
End If
 
Conn.Open(Application("HypnosisDB_ConnectionString"))
ObjRS.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
 
Dim rows
hasResults = false
If ObjRS.EOF Then
	Redim rows(0,0)
Else
	rows = ObjRS.getRows()
	hasResults = true
End If
ObjRs.close()
 
%>
 
								<%
									for i=0 To ubound(fields)
										%>
											
										<%	
									next
								%>
						<BR>
						
							<%
								If hasResults Then
									for i=0 To ubound(rows, 2)
									%>
																				
											<a href="detail.asp?EntryID=<%= rows(1, i) %>"><font face="Verdana, Arial, Helvetica" size="2"><%= rows(2, i) %>&nbsp;</font></a>
																																					 
										<BR>
										
										<%
									Next
								End If
							%>

Open in new window

0
Comment
Question by:superlative
[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
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20319168
If you just want a random row you can use the Random number function to generate the questionID value and then specify the value in your SELECT statement criteria:

"SELECT * FROM Questions WHERE questionID = " + RandomNumber();

 

You just need to determine the maximum questionID value (using the SQL MAX keyword) so that you can pass it to the RandomNumber function.

0
 
LVL 33

Accepted Solution

by:
raterus earned 2000 total points
ID: 20319180
Replace these two lines with this,

sql = "SELECT top 3 listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"


sql = sql & " ORDER BY newid(), isnull(payedmembersince, '10/10/2100') ASC, CASE Country WHEN '' THEN 'ZZZZ' ELSE Country END ASC, CASE State WHEN '' THEN 'ZZZZ' ELSE State END ASC, CASE City WHEN '' THEN 'ZZZZ' ELSE City END ASC, FullName ASC, CompanySchoolGuild ASC"
 
0
 

Author Comment

by:superlative
ID: 20319197
hi raterus, what two lines am i replaceing? am i using both of your sql lines?
0
 

Author Comment

by:superlative
ID: 20319206
hi raterus,

this line here

sql = "SELECT top 3 listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"

works great.

now how do i get the top 3 results that show up to just randomize each time so its 3 different random results?
0
 
LVL 33

Expert Comment

by:raterus
ID: 20319528
The " ORDER BY newid() ", I posted in the second sql query is going to take care of this for you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

704 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