Random Row Number Selection

Question

I am working on a website that does random selections of employees for random drug test. I am trying to figure out a report/ code using SQL Server 2008, ASP.NET, and C#. Here is an example of what I have worked on so far: I need to do is generate a report of all employees for a specific company where the employees are assign a number. Example of this code is as follows:

SELECT 
    dbo.names2.ssn, dbo.names2.firstname, dbo.names2.lastname, 
    ROW_NUMBER() over(order by dbo.names2.ssn) as RowNumber
FROM
    dbo.names2 
WHERE 
    dbo.names2.code = 8562

This query return 12 records number 1-12 with the Employees social security number, first name, and last name.
I now need to figure out a query so that when I go to my asp.net webpage and enter that I need 5 employees to be randomly tested that I get a query that returns the row number the employee is associated with in the query above on one page of the report, and on the second page of the report return the number assigned in the query above along with the employees SSN, First, and last name.

Thanks, ty

http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table — Anand, Sep 10 '13 at 16:03

db_brad · Answer 1 · 2013-09-10T20:37:31.173

0

I would ORDER BY NEWID() which generates a random GUID and SELECT TOP 5.

Edited. This query has 2 return results. 1 is the full list of employees and the other is just the list of 5 randomly selected numbers that corresponds to the rownum on the employee list.

IF (OBJECT_ID(N'tempdb..#tempTable') IS NOT NULL)
DROP TABLE #tempTable ;

CREATE TABLE #tempTable 
(
    RowNum INT  ,
    SSN VARCHAR(16) ,
    FirstName VARCHAR(64) ,
    LastName VARCHAR(64)
);

INSERT INTO [#tempTable]
        ([RowNum] ,
        [SSN] ,
        [FirstName] ,
        [LastName]
        )
SELECT  ROW_NUMBER() OVER(ORDER BY dbo.names2.ssn) AS RowNum ,
        dbo.names2.ssn ,
        dbo.names2.firstname ,
        dbo.names2.lastname
FROM    dbo.names2
WHERE   dbo.names2.code = 8562

SELECT  [RowNum] ,
        [SSN] ,
        [FirstName] ,
        [LastName] 
FROM    [#tempTable] AS tt

SELECT  TOP 5 RowNum
FROM    [#tempTable] AS tt
ORDER  BY NEWID()

edited Sep 10 '13 at 20:37

answered Sep 10 '13 at 17:53

db_brad

903
6
22

I would prefer to query out the info like you did above. Which does qualify it to be a random selection... Only problem is we have to give our client a number list of all the employees who are in the pool to be selected for a drug test. Unfortunately, for some clients we can not give the list of employees selected for a drug test for security reasons. Instead we have a two part process where the collector only has a set of random numbers and the onsite manager has the complete list of employees. So when we come out they compare the two sheets to determine who is selected for a random test. – Tyler McCoy Sep 10 '13 at 18:01
So in other words I have to have a number list of employees which their number corresponds to the list of who is going to be randomly selected for a drug test. – Tyler McCoy Sep 10 '13 at 18:03
So you want a master list of a randomly generated number/Id with all employees (or select list) that corresponds to the list of randomly selected employees? If that's right, I think I have the answer. – db_brad Sep 10 '13 at 20:19

Random Row Number Selection

1 Answers1