Reputation: 35
SELECT * FROM
(SELECT user_id, SUM(update_qty)
FROM inv_tran
WHERE code = 'Pick'
AND to_loc_id = 'CONTAINER'
AND work_group LIKE 'B%'
AND dstamp BETWEEN to_date('30/07/2021 18:00:00', 'dd/mm/yyyy hh24:mi:ss') AND
to_date('31/07/2021 06:00:00', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY user_id
ORDER BY 2 DESC
FETCH FIRST 5 ROWS ONLY)
Hi, I've got a problem which i can't figure it out how to solve it. I have this query, i'm trying to display a second one which gonna be exactly the same but the work_group LIKE 'S%'
Basicially i want to display top 5 users in work_group like B% and top 5 users in work_group like S%. I was trying to use cross join, but its giving me 50 records instead of 5 which is understandable for me.
I need this output, but from my understanding its not possible.
USERID UPDATE_QTY USERID UPDATE_QTY
1 USER1 898 USER6 252
2 USER2 516 USER7 242
3 USER3 415 USER8 132
4 USER4 325 USER9 25
5 USER5 216 USER10 4
Upvotes: 0
Views: 59
Reputation: 4694
Use a table to provide the patterns and then use the patterns in the window function to number the rows per pattern. Then join the rows from each pattern result to pair the n = 1 cases, n = 2 cases, etc.
In the following example, the second CTE term is only present to provide the test data (your inv_tran table). Just remove that CTE term when the actual table is present. If you don't really want to pair the 5 results from each pattern group, just remove the last join.
WITH patterns (pattern) AS (
SELECT 'B%' FROM dual UNION
SELECT 'S%' FROM dual
)
, inv_tran (user_id, update_qty, code, to_loc_id, work_group, dstamp) AS (
SELECT 1, 121, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
SELECT 2, 122, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
SELECT 3, 123, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
SELECT 4, 124, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
SELECT 5, 125, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
SELECT 6, 126, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
SELECT 11, 121, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
SELECT 12, 122, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
SELECT 13, 123, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
SELECT 14, 124, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
SELECT 15, 125, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
SELECT 16, 126, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual
)
, grps AS (
SELECT *
FROM (
SELECT user_id, SUM(update_qty)
, ROW_NUMBER() OVER (PARTITION BY pattern ORDER BY SUM(update_qty) DESC) AS n
, pattern
FROM inv_tran, patterns
WHERE code = 'Pick'
AND to_loc_id = 'CONTAINER'
AND work_group LIKE pattern
AND dstamp BETWEEN to_date('30/07/2021 18:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND to_date('31/07/2021 06:00:00', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY user_id, pattern
)
WHERE n <= 5
)
SELECT g1.*
, g2.*
FROM grps g1
JOIN grps g2
ON g1.n = g2.n
AND g1.pattern < g2.pattern
ORDER BY g1.n
;
Upvotes: 5
Reputation: 1270401
Hmmm . . . If I understand correctly, you can use window functions. However, you want to also aggregate by the first character of work_group
to so each user only includes one or the other work group:
SELECT *
FROM (SELECT user_id, SUM(update_qty),
SUBSTR(work_group, 1, 1) as work_group_1,
ROW_NUMBER() OVER (PARTITION BY SUBSTR(work_group, 1, 1) ORDER BY SUM(update_qty) DESC) as seqnum
FROM inv_tran
WHERE code = 'Pick' AND
to_loc_id = 'CONTAINER' AND
(work_group LIKE 'B%' OR work_group LIKE 'S%') AND
dstamp BETWEEN to_date('30/07/2021 18:00:00', 'dd/mm/yyyy hh24:mi:ss') AND
to_date('31/07/2021 06:00:00', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY user_id, SUBSTR(work_group, 1, 1)
) u
WHERE seqnum <= 5;
Upvotes: 0