Komarov
Komarov

Reputation: 35

Top 5 records, combine two queries from the same table

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

Answers (2)

Jon Armstrong
Jon Armstrong

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
;

Result: enter image description here

Upvotes: 5

Gordon Linoff
Gordon Linoff

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

Related Questions