[Solved] Oracle SQL MIN and MAX combination from same table
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Oracle SQL MIN and MAX combination from same table

Database type:

I have below mentioned data. I am looking to get max of Start message and corresponding min or success message.

Start Message Table
ID1     Timestamp_start_msg_recieved    date        jobid      message time in seconds
1234    5/14/2014 10:02:29              5/14/2014   abc        start 262
1234    5/14/2014 10:02:31              5/14/2014   abc        start 264
1234    5/14/2014 10:02:45              5/14/2014   abc        start 278
1234    5/14/2014 10:02:50              5/14/2014   abc        start 285
1234    5/14/2014 10:09:04              5/14/2014   abc        start 165
1234    5/14/2014 10:09:06              5/14/2014   abc        start 2167
1234    5/14/2014 10:09:16              5/14/2014   abc        start 2180
1234    5/14/2014 10:09:26              5/14/2014   abc        start 2190
1234    5/14/2014 11:45:11              5/14/2014   abc        start 8767
1234    5/14/2014 16:48:20              5/14/2014   abc        start 878
1234    5/14/2014 19:02:52              5/14/2014   abc        start 687
5678    5/14/2014 22:02:52              5/14/2014   pqr        start 501
5678    5/14/2014 23:10:40              5/14/2014   pqr        start 200
Success Message Table
ID1     Timestamp_success_msg_recieved  date        jobid  message time in seconds
1234    5/14/2014 10:02:52              5/14/2014   abc    successful 290
1234    5/14/2014 10:09:32              5/14/2014   abc    successful 4280 
1234    5/14/2014 11:45:15              5/14/2014   abc    successful 8774
1234    5/14/2014 11:45:18              5/14/2014   abc    successful 8777
1234    5/14/2014 11:45:19              5/14/2014   abc    successful 8778
1234    5/14/2014 11:45:25              5/14/2014   abc    successful 8784
1234    5/14/2014 16:48:22              5/14/2014   abc    successful 880 
1234    5/14/2014 19:03:00              5/14/2014   abc    successful 699
5678    5/14/2014 22:03:00              5/14/2014   pqr    successful 250
5678    5/19/2014 14:00:16              5/19/2014   pqr    successful 400

Expected Result

ID1  IMESTAMP_for_start_message TIMESTAMP_for_success_message    Date       Jobid    msg  msg start_secs success_secs
1234 5/14/2014 10:02:50         5/14/2014 10:02:52           5/14/2014  abc start success 262 290 
1234 5/14/2014 10:09:26         5/14/2014 10:09:32           5/14/2014  abc start success 2190 4280
1234 5/14/2014 11:45:11         5/14/2014 11:45:25           5/14/2014  abc start success 8767 8784
1234 5/14/2014 16:48:20         5/14/2014 16:48:22           5/14/2014  abc start success 878 880
1234 5/14/2014 19:02:52         5/14/2014 19:03:00           5/14/2014  abc start success 687 699
5678 5/14/2014 22:02:52         5/14/2014 22:03:00           5/14/2014  pqr start success 501 699
5678 5/14/2014 23:10:40         null                         5/14/2014  pqr start success 250 null
5678    null                   5/19/2014 14:00:16            5/19/2014  pqr null  success null 400

I am looking for Max of start message and start_secs to pair up with min of success message and success_secs. Tried using Temporary table using WITH clause and also used self join method. Below is my query, But WITH clause query returns MIN of overall data in the table.

Query Used:

WITH DATA AS
  (SELECT MIN(smt.column13) timestamp_for_success_message
  FROM success_table1 smt, start_table2 b
     WHERE
    (SMT.id1 = b.id1)
    AND (SMT.jobid = b.jobid)
    AND (SMT.timestamp_for_success_message_recieved >= b.timestamp_for_start_message_recieved)
  )
SELECT distinct a.timestamp_for_success_message_recieved,
  b.timestamp_for_start_message_recieved,
  b.id1,
  b.jobid
FROM data a,
  start_table2 b
order by b.timestamp_start_message_recieved, a.timestamp_for_success_message_recieved, b.jobid, b.id1;

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX start_table2_idx_id1_jobid_timestamp ON start_table2 (id1,jobid,timestamp_for_start_message_recieved);
CREATE INDEX success_table1_idx_id1_jobid_timestamp ON success_table1 (id1,jobid,timestamp_for_success_message_recieved);
The optimized query:
WITH DATA AS (SELECT
        MIN(smt.column13) timestamp_for_success_message 
    FROM
        success_table1 smt,
        start_table2 b 
    WHERE
        (SMT.id1 = b.id1) 
        AND (SMT.jobid = b.jobid) 
        AND (SMT.timestamp_for_success_message_recieved >= b.timestamp_for_start_message_recieved)) SELECT
        DISTINCT a.timestamp_for_success_message_recieved,
        b.timestamp_for_start_message_recieved,
        b.id1,
        b.jobid 
    FROM
        data a,
        start_table2 b 
    ORDER BY
        b.timestamp_start_message_recieved,
        a.timestamp_for_success_message_recieved,
        b.jobid,
        b.id1

Related Articles



* original question posted on StackOverflow here.