asked on

PL/SQL - Need to split up a result set and then select it based on the group

I need to group this result set into 4 even groups. The total rows of this result set
will always be the same number. At the sametime, I would like to be able
to send in a parameter to ONLY select the group number I need, i.e. category = 1, category = 2,
category = 3. I need to do this because I will be running this code once a week for the month.
I thought that the Ntile function would do the job but it does nto seem to work, can someone
help me?

SELECT pat.pat_id,
            pat.pat_first_name,
            pat.pat_last_name,
            pat.pat_street_addr,
            pat.pat_city,
            pat.pat_state,
            decode(pat.pat_zip, NULL, '         ', pat.pat_zip),
            pat.pat_cc_nbr,
            ntile(4) over     (order by pat.pat_id) as category
     FROM   tbf0_patient pat
     WHERE  pat.pat_cc_exp_date = 1007
       AND pat.pat_cc_nbr is NOT NULL
      AND category = 1;

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

farekat

ASKER

Cool, that worked, is there another way to be able to do this select statement without using the ntile function
and having to pass in which category I'd like to use? I'd like to avoid having to pass in this parameter at all
now...that's a double question but i was just wondering if you would know. Like how Selet TOP (returns the first)
set of rows, is there something similar to that like select first (1/4) of data, then the 2nd (1/4) of data
or is this it??

Sean Stuber

Do you know going in how many rows there will be?

Sean Stuber

if you do, you could try something like this...

where category_number and total_number_of_rows are constants you define

SELECT *
FROM (SELECT pat.pat_id, pat.pat_first_name, pat.pat_last_name, pat.pat_street_addr, pat.pat_city,
pat.pat_state, DECODE(pat.pat_zip, NULL, ' ', pat.pat_zip), pat.pat_cc_nbr, ROWNUM rn
FROM tbf0_patient pat
WHERE pat.pat_cc_exp_date = 1007
AND pat.pat_cc_nbr IS NOT NULL
AND ROWNUM <=(category_number * CEIL(total_number_of_rows / 4)))
WHERE rn > (category_number - 1) * CEIL(total_number_of_rows / 4)

farekat

ASKER

I just figured out that the number of rows are going to change, so I am thinking that I have to change my
approach around this entire thing...you still get the points thank you so much!