We help IT Professionals succeed at work.

Select random records from table minus a few specific records

Robert Francis
on
The following attached code works fine for starters. It displays every employee in the employee table. What I want it to do is randomly select two employees, but here is the kicker. I want to omit a couple of the employees from the selection pool.

The "employee" column in the "employee" table is a 3 digit number.
I know the employee numbers of the employees I would like to omit

Any ideas would be greatly appreciated

Thanks
<% 
set connGlobal = server.createobject("ADODB.Connection")
connGlobal.Open "DRIVER={SQL Server}; Server=prince03; Database=prince; uid=sa; pwd=*******"

strSQL = "SELECT first_name, last_name, employee FROM employee WHERE status='active'"		
set rs = connGlobal.Execute (strSQL)
Do until rs.eof
%>
<p><%response.write rs("employee")%> - <%response.write rs("last_name")%>, <%response.write rs("first_name")%> </p>
<%
rs.Movenext
loop
%>

Open in new window

Comment
Watch Question

Commented:
if you have a unique ID of a primary key you can easily pull out random records based on the unique ID.
Robert FrancisDirector of Continuous Improvement

Author

Commented:
The employee column is a unique ID, but I don't know how to change the code to get the correct results.

Commented:
sorry forgot the query, lets say you wanted 5 random records you could do

SELECT top 5 first_name, last_name, employee FROM employee WHERE status='active' Order by NEWID()"
Robert FrancisDirector of Continuous Improvement

Author

Commented:
That's cool, but it doesn't account for the few employees I don;t want in the pool
Top Expert 2011
Commented:
Adding WHERE clause to query submitted by @rbgCODE which would filter the employee you do not want to display

SELECT top 5 first_name, last_name, employee FROM employee WHERE status='active'
and employee not in (100, 101, 104)
Order by NEWID()

Commented:
I didn't even get an assist on the answer I just missed one point?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.