Quote:
|
Originally Posted by r937
Code:
SELECT temp_id
, a_id
, SUM(emp_cnt) AS emp_cnt
FROM (
/* insert your query here */
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
GROUP
BY temp_id
, a_id
 |
One more question. I have a further requirement - given a user specified number say 4000, a program needs to pick randomly 4000 temp_ids from the temp_ids existing in the database.
I could hold in memory all the temp_ids that are the result of the SQL (Please refer to the SQL suggested by you, from above).
But I am not sure if I want to a hold large number of temp_ids in memory and then use random to pick temp_ids.
So is there a way in Oracle 10g that allows usage of random in SQL statement itself?..so that the SQL picks 4000 DIFFERENT temp_ids from the database?
If so, can it be used in the type of SQL from above?
Thanks in advance for your input.