Selecting two random (different)column values - seems tricky

[sbasak1]

A table has two columns - firstname & lastname

Now, I want to randomly select a firstname and lastname.
The trick is, the firstname and lastname must NOT be selected from same row!

I tried to use dbms_random.value(1,1000) and got the names, the problem is how do I concat the two fields

If I use

select firstname, lastname from customer where....

then I get values from same row!

Thanks

[abhaysk]

The requirement seems foolish.. though u can achieve..

Abhay.

[DaPi]

I agree with Abhay, it's a tough problem and the requirement seems strange - how much time should one spend on this?

How "random" should this be?
One method is to make a random number part one of your selected columns, sort on this and take the first row. You could apply this technique like:

select * from (
select a.first, b.last, dbms_random.random ran
from customer a, customer b
where a.pk <> b.pk
order by ran
) where rownum = 1

However, if the table is not small, this might blow up. Using SAMPLE might help, but this is not truely random.

If the table is big, you might need to use this technique to get a random first name in a one-row in-line view and then anti-join this to the customer table to get first/last combinations; these can again have a random row selected. You'll end up with SELECT statements nested six deep - I'm not motivated to code that!

[sbasak1]

Thanks for the reply guys!

Basically, I need to generate real life looking name address details for volume testing purpose, so, I thought if I keep a table of firstname, lastname, address, city, postcode - then from there I can generate some more realistic data. So, I posted the question.

Definitely, I can handle it using any programming language like VB etc. But I was wondering whether that can be achieved by using PL/SQL only.

One plausible idea seems to be using a PL/SQL function to generate random first and last name and then concatenating them.

The table has around 50,000 rows!

[DaPi]

If you're going to use PL/SQL, why not set up a cursor over the customer table sorted by dbms_random.random, then in the loop take first name from the first row, last name from the second, address from the third, etc. - then start again with the first name from the (say) sixth row. This will be random and will protect the anonymity of the customers (if that's an issue).

This will give you about 10'000 rows - repeat ad-lib (don't reset the seed) if you need more.