sql query to find priority jobs

Posted By: Anonymous

I have got a sql table called x

|priority     |CategoryType |status       |CategoryGroup|
|-------------|-------------|-------------|-------------|
|7            |NoFeedBF     |ready        |NoFeed|
|5            |FeedWIL      |ready        |Feed|
|6            |FeedSIL      |ready        |Feed|
|7            |NoFeedDRM    |ready        |NoFeed|
|8            |NoFeedFx     |processing   |NoFeed|
|9            |NoFeedHK     |             |NoFeed|
|10           |Common       |ready        |Common|

where i want to retrieve rows based on priority, category group and status

when ever a job comes, it come to this table and mark it as ready.
when ever a job is picked up, it mark status as processing

Rules are as follows:

1. no feed and feed should not run together
2. common can run with both feed and nofeed if in ready status
3. if status is all ready, then highest priority jobs has to be picked (e.g common and nofeedhk)
4. if NoFeed is processing (e.g NoFeedFx), then highest priority jobs from same category group(NoFeed) which are in ready status has to be picked up (e.g common and NoFeedDRM,NoFeedBF)
5. If feed is in processing status (e.g FeedWIL), it can pick up another new feed from same category group (feed) which come recently if in ready status and of highest priority (e.g common and FeedSIL)

Tried to formulate a query, but not able to find successful one for all scenarios (like including common and multiple records of same priority etc)

select category_type, priority,status, category_group from x where category_group in  
(select distinct category_group from x 
where status = 
(select distinct case 
when (select count(status) from x where status='processing')>=1 then 'processing'
when (select count(status) from x where status='processing')<1 then 'ready'
else null
end "status"
from x)) 
and upper(status) IN ('READY','PROCESSING') 
order by priority asc fetch first 1 rows only;

please help if you can form a better query to match the rules. thanks a lot

Example 1:

Ouptutexpected-1 (see rule-3)

Example 2:

Ouptutexpected (see rule-4)

Example 3:

Ouptutexpected (see rule-4)

Example 4:

Ouptutexpected (see rule-5)

Solution

Here is one way that you could do it – note that I have mimicked your table with all the different sets of inputs in it, which your actual table wouldn’t have, so you’d have to amend the query to remove references to the example_no column.

I added a 5th case where there are ready tasks for NoFeed and Feed with the same priority; I’ve picked NoFeed to have a higher priority than Feed, so I only show that category. If you need to change it so that the Feed task is shown instead or both tasks are shown, you’d need to amend the order by clause in the dense_rank.

WITH your_table AS (SELECT 1 example_no, 7 priority, 'NoFeedBF' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 1 example_no, 5 priority, 'FeedWIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 1 example_no, 6 priority, 'FeedSIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 1 example_no, 7 priority, 'NoFeedDRM' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 1 example_no, 8 priority, 'NoFeedFx' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 1 example_no, 9 priority, 'NoFeedHK' CategoryType, NULL status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 1 example_no, 10 priority, 'Common' CategoryType, 'ready' status, 'Common' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 7 priority, 'NoFeedBF' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 5 priority, 'FeedWIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 6 priority, 'FeedSIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 7 priority, 'NoFeedDRM' CategoryType, 'running' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 8 priority, 'NoFeedFx' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 9 priority, 'NoFeedHK' CategoryType, NULL status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 2 example_no, 10 priority, 'Common' CategoryType, 'ready' status, 'Common' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 7 priority, 'NoFeedBF' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 5 priority, 'FeedWIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 6 priority, 'FeedSIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 7 priority, 'NoFeedDRM' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 6 priority, 'NoFeedADHOC' CategoryType, 'running' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 8 priority, 'NoFeedFx' CategoryType, NULL status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 9 priority, 'NoFeedHK' CategoryType, NULL status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 3 example_no, 10 priority, 'Common' CategoryType, 'ready' status, 'Common' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 7 priority, 'NoFeedBF' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 5 priority, 'FeedWIL' CategoryType, 'running' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 6 priority, 'FeedSIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 7 priority, 'NoFeedDRM' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 6 priority, 'NoFeedADHOC' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 8 priority, 'NoFeedFx' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 9 priority, 'NoFeedHK' CategoryType, NULL status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 4 example_no, 10 priority, 'Common' CategoryType, 'ready' status, 'Common' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 7 priority, 'NoFeedBF' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 8 priority, 'FeedWIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 6 priority, 'FeedSIL' CategoryType, 'ready' status, 'Feed' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 7 priority, 'NoFeedDRM' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 8 priority, 'NoFeedFx' CategoryType, 'ready' status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 9 priority, 'NoFeedHK' CategoryType, NULL status, 'NoFeed' CategoryGroup FROM dual UNION ALL
                    SELECT 5 example_no, 10 priority, 'Common' CategoryType, 'ready' status, 'Common' CategoryGroup FROM dual),
   curr_running AS (SELECT example_no,
                           priority,
                           categorytype,
                           status,
                           categorygroup,
                           nofeed_running,
                           feed_running,
                           CASE WHEN categorygroup = 'NoFeed' AND feed_running = 1 THEN NULL
                                WHEN categorygroup = 'Feed' AND nofeed_running = 1 THEN NULL
                                ELSE categorygroup
                           END new_categorygroup
                    FROM   (SELECT example_no,
                                   priority,
                                   categorytype,
                                   status,
                                   categorygroup,
                                   MAX(CASE WHEN categorygroup = 'NoFeed' AND status = 'running' THEN 1 END) OVER (PARTITION BY example_no) nofeed_running,
                                   MAX(CASE WHEN categorygroup = 'Feed' AND status = 'running' THEN 1 END) OVER (PARTITION BY example_no) feed_running
                            FROM   your_table
                            WHERE  status IN ('ready', 'running'))),
        results AS (SELECT example_no,
                           priority,
                           categorytype,
                           status,
                           categorygroup,
                           new_categorygroup,
                           nofeed_running,
                           feed_running,
                           dense_rank() OVER (PARTITION BY example_no, CASE WHEN new_categorygroup IN ('NoFeed', 'Feed') THEN 'Feed' ELSE new_categorygroup END ORDER BY priority DESC, new_categorygroup DESC) dr
                    FROM   curr_running
                    WHERE  status = 'ready'
                    AND    new_categorygroup IS NOT NULL)
SELECT example_no,
       priority,
       categorytype,
       status,
       categorygroup
FROM   results
WHERE  dr = 1
ORDER BY example_no,
         dr,
         categorytype;

EXAMPLE_NO   PRIORITY CATEGORYTYPE STATUS  CATEGORYGROUP
---------- ---------- ------------ ------- -------------
         1         10 Common       ready   Common
         1          8 NoFeedFx     ready   NoFeed
         2         10 Common       ready   Common
         2          8 NoFeedFx     ready   NoFeed
         3         10 Common       ready   Common
         3          7 NoFeedBF     ready   NoFeed
         3          7 NoFeedDRM    ready   NoFeed
         4         10 Common       ready   Common
         4          6 FeedSIL      ready   Feed
         5         10 Common       ready   Common
         5          8 NoFeedFx     ready   NoFeed

This works by using the MAX analytic function to determine if there’s already a running task in each set of data for the Feed and NoFeed categories, and then using that to output a new version of the categorygroup column, where the category is set to null for the group we don’t want to run at the same time as a task in the other group.

Once we have that information, we then do a dense_rank to find the ready rows with the highest dense rank per category that we’re interested in.

Finally, we select only those rows with a dense_rank of 1 – i.e. the highest priority rows.

Answered By: Anonymous

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.