Hi,

I have an interesting task.  We have a client who's company has several departments.  They want to randomly select 2 employees from each department on a regular basis for the purpose of random drug tests.  Can anyone think of a way to randomly select 2 entries in each dpeartment using SQL?

DBISAM V3.30

Regards
Al

Alex

>I have an interesting task. We have a client who's company has several departments. They want to randomly select 2 employees from each department on a regular basis for the purpose of random drug tests. Can anyone think of a way to randomly select 2 entries in each dpeartment using SQL?

Personally I'd recommend employees of such a company to get another job but on a technical front your options depend on what the structure of the table is.

If you post the table structures and then someone (probably John Hay) may be able to give you a solution.

Roy Lambert [Team Elevate]

There are plenty of algorithms on the web for picking items from a list. I'd read
the RowID or some other such thing from the table, then apply one of those. With
this sort of thing you want to show that you have done it 100% properly, otherwise
you are subject to legal challenge. If you found a solution on something like
StackOverflow, it should be good. I'd also run it a few thousand times to ensure
that there is a good spread of coverage too. And check the random number generator
is good (the RTL one may not be enough).

/Matthew Jones/

Roy Lambert wrote:

Personally I'd recommend employees of such a company to get another job but on a technical front your options depend on what the structure of the table is.

Don't worry Roy, many employees working in the Australian mining industry get well rewarded for their inconvenience

Basically the table would be pretty straight forward, for example, the two fields of importance would be:

* Employee Name
* Department

eg

Smith Joe, Manufacturing
Dawson John, Manufacturing
Stewart Hayley, Manufacturing
Carlisle Jack,  R&D
Blithe Peta, R&D
Jackson Dan, R&D

Of course the list in each department would be much longer but I want to say randomly select 1 person from each department.

Cheers
Al

Alex

>Don't worry Roy, many employees working in the Australian mining industry get well rewarded for their inconvenience

Well as long as they don't stick needles in

>Basically the table would be pretty straight forward, for example, the two fields of importance would be:
>
>* Employee Name
>* Department

That's what you want to come out but what are the actual keys. Is there an employee ID and a department code or are they one code? If they are one code could you have an employee 1 in department A and in departmnet B? Are all of the employees in the same table or are they split into a table per department. Is the department code part of the employee ID or a separate field. I've seen these and other variations.

As Matthew said there plenty of algorithms out there but you need to work out what you're doing first. So far we know what output you want but not what the inputs are.

Roy Lambert [Team Elevate]

Yes there are employee Ids and department codes.  You could replace my previous example with codes if you like.  An employee cannot belong to 2 departments, so there might be 50 emps in one department, and 40 in another.  I want to select 2 employees from each of those departments.

I have looked around for algorithms on the Internet but most refer to a random function which I don't believe DBISAM V3 has so I need to try and think otuside the box.  Was thinking maybe that grabbing the milliseconds from the current time would be a basis for attaining an, in effect random number.

Alex,

Why do you need to just use SQL - this would be quite trivial in Delphi
and even EDB but doing it purely in DBISAM (and v3) SQL is tougher.

Milliseconds might work though your precision is likely around 16ms
(hardware clock) so if you just use it then how do you pick if there are
more then 62 staff?

Do you need a random selection of 2 people or more of a random sequence
over time? If latter then you basically should not get same person until
everybody else has been tested as well (or maybe at least annually or
so). For this you'd need to store the historic picks.

Raul



On 5/1/2013 9:07 PM, Alex Vastich wrote:
> Yes there are employee Ids and department codes.  You could replace my previous example with codes if you like.  An employee cannot belong to 2 departments, so there might be 50 emps in one department, and 40 in another.  I want to select 2 employees from each of those departments.
>
> I have looked around for algorithms on the Internet but most refer to a random function which I don't believe DBISAM V3 has so I need to try and think otuside the box.  Was thinking maybe that grabbing the milliseconds from the current time would be a basis for attaining an, in effect random number.
>

Alex


Check out the manual V4 does have this. Not sure about V3

RAND Function
The RAND function returns a random number:
RAND([RANGE range of random values])

But that's not where your problem is. If you simply wanted two random employees from the table that would be easy. Your problem is that you want two random employees from a an unspecified number of subsets of the table.

Are there any restrictions such as an employee can't be selected twice in a row.


Roy Lambert [Team Elevate]

Alex


Looking through the newsgroup I found a previous post of mine


SELECT rand(1000000) AS rd,questionnumber FROM questions
ORDER BY rd
TOP 40

But 1) you need the RAND function and 2) it won't work with your multiple departments

Roy Lambert [Team Elevate]

Alex


I knew I'd seen something like this before

http://rickosborne.org/blog/2008/01/sql-getting-top-n-rows-for-a-grouped-query/

John Hay also posted a solution to this type of problem (I just can't remember which newsgroup, or come up with a search term to find it)

Again you will need some sort of random number generator for it to work though.

Roy Lambert [Team Elevate]