Group by highest/MAX value and calculate difference

Question

This is MYSQL/ SQL running against data on Oracle server. I have a SQL table that stores data as follows:

{IMPORTID Created  IMPORTREQUESTID    STARTDATE       ENDDATE
1156     04/21/2017 (12 AM)    63833           4/23/2017 18:18 4/23/2017 18:19
1156     04/21/2017 (12 AM)    63833           4/23/2017 18:18 4/23/2017 18:18
1156     04/21/2017 (12 AM)   63832           4/23/2017 17:56 4/23/2017 17:57
1156     04/21/2017 (12 AM)    63832           4/23/2017 17:56 4/23/2017 17:57
1156     04/21/2017 (12 AM)   63832           4/23/2017 17:56 4/23/2017 17:57}

I want the Query to go through the table and find the HIGHEST or MAX IMPORTREQUESTID and max ENDDATE for any given IMPORTID. And then calculate the difference between CREATED and max(ENDDATE) for any given IMPORTID, once it finds the row containing maximum values. Again, I only want the max IMPORTREQUESTID and ENDDATE tied to a given IMPORTID. (An IMPORTID will have several rows with different IMPORTREQUESTIDS)

So, for the given data above my query should return only 1 row as follows:

{IMPORTID Created  IMPORTREQUESTID    STARTDATE       ENDDATE         DIFF
1156    04/21/2017 12 AM    63833  4/23/2017 18:18  4/23/2017 18:19  **6:19**}

So, in this case it found that IMPORTREQUESTID 63833 was the highest value and THEN enddate of 18:19 was highest time. It then picked that row and calculated difference between created and ENDDATE.

Here is query I have so far:

     SELECT ImportID 
 ,A.created
 ,IMPORTSTATUS 
 ,IMPORTCANONICALTYPE  
 , to_char(StartDate,'MM/DD/YYYY HH24:MI') StartDate 
 , to_char(EndDate,'MM/DD/YYYY HH24:MI') EndDate  
 , ((A.created-A.Enddate)*60*24) as MinDiff 
 , ImportRequestID

FROM (SELECT DISTINCT importrequestpartdone.ImportID
,importrequestpartdone.IMPORTCANONICALTYPE 
 ,importrequestpartdone.IMPORTSTATUS 
 ,importrequestpartdone.ImportRequestID 
 , importrequestpartdone.StartDate 
 , importrequestpartdone.EndDate 
,import.created
     From import,baywatchnosnap.importrequestpartdone@db1 
 where contextid=10114
and import.id=importrequestpartdone.importid
and importrequestpartdone.importstatus='2I') A

This returns all the rows. I only want the max values as indicated above. So, each IMPORTID, should only be returning 1 row.

Can you explain how you arrived at a diff of `6:19` for your sample data? — Tim Biegeleisen, May 02 '17 at 05:47
Should we ignore the time component of `StartDate`, `EndDate` or both? — toonice, May 02 '17 at 06:24
Can a smaller `ImportRequestID` have a more recent `EndDate`, or vice versa? — toonice, May 02 '17 at 06:46
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 02 '17 at 07:14
I don't understand 'running MySQL against oracle server'... and this date formatting stuff is just ridiculous — Strawberry, May 02 '17 at 07:15

score 0 · Answer 1 · answered May 02 '17 at 06:03

Even though I don't understand how you arrived at a difference of 6:19, as the difference is just 1 minute

But Try this:-

SELECT importrequestpartdone.ImportID 
 ,import.created
 ,importrequestpartdone.IMPORTSTATUS 
 ,importrequestpartdone.IMPORTCANONICALTYPE  
 , to_char(StartDate,'MM/DD/YYYY HH24:MI') StartDate 
 , to_char(EndDate,'MM/DD/YYYY HH24:MI') EndDate  
 , ((A.created-A.Enddate)*60*24) as MinDiff 
 , importrequestpartdone.ImportRequestID

FROM baywatchnosnap.importrequestpartdone@db1
inner join

(SELECT importrequestpartdone.ImportID
 ,max(importrequestpartdone.ImportRequestID) as max_req_id
 , max(importrequestpartdone.EndDate) as max_end
     From import,baywatchnosnap.importrequestpartdone@db1  
 where contextid=10114
and import.id=importrequestpartdone.importid
and importrequestpartdone.importstatus='2I'
group by importrequestpartdone.ImportID ) A

on a.ImportID=importrequestpartdone.ImportID and a.max_req_id=importrequestpartdone.ImportRequestID
and a.max_end=importrequestpartdone.EndDate

inner join
import
on import.id=importrequestpartdone.importid
where contextid=10114
and importrequestpartdone.importstatus='2I';

Group by highest/MAX value and calculate difference

1 Answers1