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:
- no feed and feed should not run together
- common can run with both feed and nofeed if in ready status
- if status is all ready, then highest priority jobs has to be picked (e.g common and nofeedhk)
- 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)
- 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
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.