Select MAX value and GROUP BY-CodePudding

I'm trying to extract ID values where End_Time for type A is not equal to Max End_Time of each System_Id

ID     System_Id Type  End_Time
1        1         A    10:00
1        1         B    12:00
1        2         A    14:00
1        2         B    14:00
2        3         A    07:00
2        3         B    07:00

So end result should be:

ID    System_ID   Type  End_Time
1        1          A     10:00

For now I was trying this:

Select ID 
From TestTable t 
WHERE t.Type LIKE 'A'
AND t.End_Time < 
(
    SELECT MAX(t2.End_Time)
    From TestTable t2
    Order by System_Id
)

I think this query should be somewhat ok, because if I ask in the subquery for a specific ID, and outside it - it returns a proper answer. I think that I need to somehow group it also by ID, however I don't really know how. Database is Oracle if that changes some things.

CodePudding user response：

Use EXISTS:

SELECT t1.*
FROM TestTable t1
WHERE t1.Type = 'A'
  AND EXISTS (
        SELECT *
        FROM TestTable t2
        WHERE t2.ID = t1.ID AND t2.End_Time > t1.End_Time 
      );

or a self join:

SELECT DISTINCT t1.*
FROM TestTable t1 INNER JOIN TestTable t2
ON t2.ID = t1.ID AND t2.End_Time > t1.End_Time
WHERE t1.Type = 'A';

See the demo.

CodePudding user response：

You can use the RANK analytic function:

SELECT ID, System_Id, Type, End_Time
FROM   (
  SELECT t.*,
         RANK() OVER (PARTITION BY system_id ORDER BY end_time DESC) AS rnk
  FROM   testtable t
)
WHERE  type = 'A'
AND    rnk > 1;

Or, the MAX analytic function:

SELECT ID, System_Id, Type, End_Time
FROM   (
  SELECT t.*,
         MAX(end_time) OVER (PARTITION BY system_id) AS max_end_time
  FROM   testtable t
)
WHERE  type = 'A'
AND    end_time < max_end_time;

Or, can fix your query by correlating the sub-query to the outer query:

SELECT *
FROM   TestTable t 
WHERE  t.Type = 'A'
AND    t.End_Time < ( SELECT MAX(t2.End_Time)
                      FROM   TestTable t2
                      WHERE  t.system_id = t2.system_id )

Note: The first two queries only SELECT ... FROM TestTable once and the outer query is purely for filtering the existing data. The final query SELECT ... FROM TestTable twice and will likely be less efficient.

Then, for the sample data:

CREATE TABLE testtable (ID, System_Id, Type, End_Time) AS
SELECT 1, 1, 'A', INTERVAL '10:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 1, 'B', INTERVAL '12:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 2, 'A', INTERVAL '14:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, 2, 'B', INTERVAL '14:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, 3, 'A', INTERVAL '07:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 2, 3, 'B', INTERVAL '07:00' HOUR TO MINUTE FROM DUAL;

All queries output:

ID SYSTEM_ID TYPE END_TIME

1 1 A 00 10:00:00

ID	SYSTEM_ID	TYPE	END_TIME
1	1	A	00 10:00:00

db<>fiddle here

CodePudding user response：

Yes, you are right, GROUP BY should be used for this query. Please try:

SELECT t1.ID
FROM   Testtable T1
       INNER JOIN (
         SELECT ID, MAX(END_TIME) AS END_TIME_MAX
         FROM Testtable
         GROUP BY ID
       ) T2
       ON T1.ID = T2.ID
WHERE  T1.END_TIME < T2.END_TIME_MAX
AND    T1.Type = 'A'