Skip to content
Fix Code Error

sql query to find priority jobs

June 16, 2021 by Code Error
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:

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

Ouptutexpected-1 (see rule-3)

priority CategoryType status CategoryGroup
10 Common ready Common
8 NoFeedFx ready NoFeed

Example 2:

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

Ouptutexpected (see rule-4)

priority CategoryType status CategoryGroup
10 Common ready Common
8 NoFeedFx ready NoFeed

Example 3:

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

Ouptutexpected (see rule-4)

priority CategoryType status CategoryGroup
10 Common ready Common
7 NoFeedBF ready NoFeed
7 NoFeedDRM ready NoFeed

Example 4:

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

Ouptutexpected (see rule-5)

priority CategoryType status CategoryGroup
10 Common ready Common
6 FeedSIL ready Feed

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

Related Articles

  • What is the worst programming language you ever worked with?
  • Sorting a 2D string array in c
  • How can I add class active if the element click on the vue…
  • Nested ng-repeat
  • Eclipse will not start and I haven't changed anything
  • SQL query return data from multiple tables
  • Golang worker pools - Queue new jobs from within jobs
  • How to create ranges and synonym constants in C#?
  • Relational room database: The class must be either entity or…
  • Being served a response directly from the cache when…

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

Post navigation

Previous Post:

How can this count return 0 if no row match?

Next Post:

Using PrimeNg locale with ngx-translate

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Get code errors & solutions at akashmittal.com
© 2022 Fix Code Error