Collect data from a table with two level of GROUP BY-CodePudding

I am trying to collect data from an Oracle database table with GROUP BY. and I think that I need two levels of GROUP BY, but I do not know how to complete my query.

I have a STATUS table with millions of statuses this way:

REQUEST    STATUS
-------    -----------
ID      -> REQUEST_ID
...        ID
           STATUS_CODE
           ....

Example how a request flow looks like (STATUS table):

SELECT ... FROM STATUS WHERE REQUEST_ID = 1 ORDER BY ID;

ID      REQUEST_ID  STATUS_CODE  STATUS_ALIAS                       CREATED
1       1           201          REQUEST_SAVED
2       1           204          REQUEST_SIGNATURE_VALID
3       1           210          REQUEST_XML_VALID
4       1           280          REQUEST_ACCEPTED

5       1           310          SENT_TO_SYSTEM_1_FOR_VERIFICATION
6       1           320          SENT_TO_SYSTEM_2_FOR_VERIFICATION
7       1           521          SYSTEM_1_VERIFICATION_ERROR
8       1           511          SYSTEM_2_VERIFICATION_ERROR

24880   1           310          SENT_TO_SYSTEM_1_FOR_VERIFICATION
24881   1           320          SENT_TO_SYSTEM_2_FOR_VERIFICATION
24885   1           620          SYSTEM_1_VERIFICATION_TIMEOUT
24886   1           610          SYSTEM_2_VERIFICATION_TIMEOUT

24887   1           310          SENT_TO_SYSTEM_1_FOR_VERIFICATION
24888   1           320          SENT_TO_SYSTEM_2_FOR_VERIFICATION
.....

I would like to collect REQUEST_IDs that are in the VERIFICATION state, but not TIMEOUTED yet, like this:

24887   1           310          SENT_TO_SYSTEM_1_FOR_VERIFICATION
.....

This is how I select that data:

SELECT REQUEST_ID, STATUS_CODE, MAX(ID) FROM STATUS
GROUP BY REQUEST_ID, STATUS_CODE HAVING STATUS_CODE = 310;

REQUEST_ID  STATUS_CODE  MAX(ID)
1           310          24887

This shows properly the ID from where I need to filter the grouped STATUS records REQUEST_ID, but when I combined this query with an outer SELECT to show the REQUEST_IDs, it does not work.

This is my best try so far:

SELECT T1.REQUEST_ID FROM STATUS T1
GROUP BY T1.REQUEST_ID, T1.ID HAVING T1.ID >= (
   SELECT MAX(ID) FROM STATUS T2
   GROUP BY T2.REQUEST_ID, T2.STATUS_CODE
   HAVING T2.STATUS_CODE IN (310, 320) AND NOT IN (610, 620)
);

ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"

CodePudding user response：

I might be confused, but I think what you need is just:

SELECT REQUEST_ID, STATUS_CODE, MAX(ID) 
  FROM STATUS
 WHERE STATUS_CODE IN (310, 320)
 GROUP BY REQUEST_ID, STATUS_CODE;

This T2.STATUS_CODE IN (310, 320) AND NOT IN (610, 620) makes no sense since when you specify the status code to be in 310/320 it will for sure not be in 610/620.

CodePudding user response：

In HAVING T2.STATUS_CODE IN (310, 320) AND NOT IN (610, 620) the second clause does not add anything as if it is in (310,320) it cannot be in (610,620). See the dbFiddle link below for schema, testing and other queries.

SELECT 
   REQUEST_ID, 
   STATUS_CODE, 
   MAX(ID) AS MAX_ID
 FROM STATUS
 WHERE STATUS_CODE IN (310, 320)
 GROUP BY 
   REQUEST_ID, 
   STATUS_CODE;

REQUEST_ID | STATUS_CODE | MAX_ID
---------: | ----------: | -----:
         1 |         310 |  24887
         1 |         320 |  24888

db<>fiddle here