Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Daxa.

Asked: August 15, 2016 - 4:17 pm UTC

Last updated: August 16, 2016 - 2:19 am UTC

Version: Oracle 11

Viewed 1000+ times

You Asked

HI
We do have store procedure and CURSOR is looking for random id per month.We can see result like

one visit from 08/may/2016
2 - 09/may/2016
5 - 11/may/2016
20 - 12/may/2016
2 - 18/may/2016

Can you tell me why 20 visits get selected from 12th may?

SELECT v.qi_visit_id visit_id,
ROUND(dbms_random.value(1,10000000)) random_id,
(SELECT COUNT(*)
FROM table
WHERE facility_id = sp.facility_id
AND qi_visit_id = v.qi_visit_id
AND global_sample_select_id = 1
AND YEAR = v.year
AND MONTH = (v.quarter-1)*3 + v.month
AND status = 0
) global_sampling_status
FROM table1 sp,
table2 v
WHERE v.qi_visit_id = sp.qi_visit_id
AND v.facility_id = p_facility_id
AND sp.status = 0
AND v.reabstracted_case <> 0
AND sp.sampling_population_id = v_sampling_population_id
AND v.year = p_year
AND v.quarter = p_quarter
AND v.month = v_month
ORDER BY global_sampling_status DESC,
random_id;

Thanks

and Connor said...

I'm not entirely sure what you are asking ? Are you looking to get random sample of rows ? I don't see anything in your query that restricts the sample size, so besides random ordering, you should see all the data.

If I do a random sample, the random spread looks ok to me, eg


SQL> create table t
  2  as
  3    select trunc(sysdate,'YYYY')+rownum/10 x, rpad(rownum,100) y
  4    from dba_objects
  5    where rownum <= 3650
  6    order by last_ddl_time desc;

Table created.

SQL>
SQL>
SQL> select f, count(*)
  2  from (
  3  select *
  4  from
  5   ( select trunc(x) f
  6     from   t
  7     order by dbms_random.value
  8    )
  9  where rownum < 100
 10  )
 11  group by f
 12  order by 2 desc, 1;

F           COUNT(*)
--------- ----------
01-JAN-16          2
13-JAN-16          2
29-JAN-16          2
09-FEB-16          2
16-FEB-16          2
31-MAR-16          2
08-JUL-16          2
03-AUG-16          2
05-AUG-16          2
06-SEP-16          2
12-OCT-16          2
24-OCT-16          2
03-JAN-16          1
12-JAN-16          1
23-JAN-16          1
24-JAN-16          1
10-FEB-16          1
15-FEB-16          1
21-FEB-16          1
26-FEB-16          1
12-MAR-16          1
13-MAR-16          1
17-MAR-16          1
19-MAR-16          1
20-MAR-16          1
26-MAR-16          1
27-MAR-16          1
03-APR-16          1
04-APR-16          1
08-APR-16          1
21-APR-16          1
25-APR-16          1
26-APR-16          1
29-APR-16          1
01-MAY-16          1
02-MAY-16          1
12-MAY-16          1
13-MAY-16          1
18-MAY-16          1
28-MAY-16          1
02-JUN-16          1
11-JUN-16          1
12-JUN-16          1
13-JUN-16          1
17-JUN-16          1
01-JUL-16          1
05-JUL-16          1
09-JUL-16          1
13-JUL-16          1
16-JUL-16          1
17-JUL-16          1
24-JUL-16          1
25-JUL-16          1
27-JUL-16          1
29-JUL-16          1
01-AUG-16          1
02-AUG-16          1
11-AUG-16          1
16-AUG-16          1
17-AUG-16          1
19-AUG-16          1
20-AUG-16          1
28-AUG-16          1
03-SEP-16          1
05-SEP-16          1
09-SEP-16          1
11-SEP-16          1
12-SEP-16          1
15-SEP-16          1
16-SEP-16          1
19-SEP-16          1
26-SEP-16          1
01-OCT-16          1
03-OCT-16          1
06-OCT-16          1
26-OCT-16          1
28-OCT-16          1
07-NOV-16          1
15-NOV-16          1
17-NOV-16          1
18-NOV-16          1
22-NOV-16          1
01-DEC-16          1
02-DEC-16          1
04-DEC-16          1
17-DEC-16          1
28-DEC-16          1

87 rows selected.

SQL>
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

rendom_id

Daxa, August 16, 2016 - 5:54 pm UTC

We just add ROWNUM column after rendom_id and it works.
Thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library