Results 1 to 3 of 3

Thread: Random usuage

  1. #1
    Join Date
    Jun 2009
    Posts
    10

    Unanswered: Random usuage

    Hi all,

    I currently have this sql that fetches temp_ids whose emp_cnt > 0 from the database. We use Oracle 10g.

    Code:
    SELECT temp_id,
             SUM(emp_cnt) AS emp_cnt
      FROM (
                  select a.temp_id, 
                  a.id as a_id, 
                  b.id as b_id,
                  (select count(*) cnt from EMP where id = b.id) as EMP_CNT
           from DEPT a, DEPT b
           where  a.domain = 'X'
           and b.domain <> 'X'
           and a.temp_id = b.temp_id
           AND b.temp_id in (select temp_id from DEPT group by temp_id having count(*) > 2)
           ) AS derivedtable
    where emp_cnt > 0 
    GROUP
        BY temp_id
         , a_id
    I have a further requirement - given a user specified number say 400, a program needs to pick randomly 400 temp_ids with unique emp_cnt from the temp_ids existing in the database.

    I could hold in memory all the temp_ids in the program itself. But I am not sure if I want to a hold large number of temp_ids in memory and then use java random to pick temp_ids.

    So is there a way in Oracle 10g that allows usage of random in SQL statement itself?

    Any input related to my requirement is highly appreciated.

    Thanks in advance for your time and interest.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So is there a way in Oracle 10g that allows usage of random in SQL statement itself?


    SELECT ..... SAMPLE may accomplish what you desire
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select temp_id,emp_cnt
    from
    (SELECT temp_id,
             SUM(emp_cnt) AS emp_cnt
      FROM (
                  select a.temp_id, 
                  a.id as a_id, 
                  b.id as b_id,
                  (select count(*) cnt from EMP where id = b.id) as EMP_CNT
           from DEPT a, DEPT b
           where  a.domain = 'X'
           and b.domain <> 'X'
           and a.temp_id = b.temp_id
           AND b.temp_id in (select temp_id from DEPT group by temp_id having count(*) > 2)
           ) AS derivedtable
    where emp_cnt > 0 
    GROUP
        BY temp_id
         , a_id 
    order by DBMS_RANDOM.RANDOM)
    where rownum <= 300
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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