Selecting two random (different)column values - seems tricky
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Selecting two random (different)column values - seems tricky

  1. #1
    Join Date
    Nov 2003
    Posts
    37

    Angry Selecting two random (different)column values - seems tricky

    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

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    The requirement seems foolish.. though u can achieve..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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!

  4. #4
    Join Date
    Nov 2003
    Posts
    37
    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!

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width