How to join max/min date column on select with last date where data on it

I have to join one table with 2 parameters to show last update status of detail to check finish/ongoing with MAX(…)/MIN(…). What can i do for this?

I have tried LEFT JOIN but it return to null

SELECT a.showonly 
     , a.want_this_parameter_lastdate
     , min(a.xdate) start
     , max(a.xdate) stop
     , b.lastdate
  FROM samedatabase a
       LEFT JOIN (SELECT xdate lastdate
                       , want_this_parameter_lastdate 
                    FROM samedatabase 
                   WHERE ROWNUM = 1 
                  ORDER BY xdate DESC) b
              ON a.want_this_parameter_lastdate = b.want_this_parameter_lastdate
 WHERE something IN (SELECT DISTINCT equalsomething 
                       FROM another WHERE input like '...') 
   AND xdate> sysdate - 7
GROUP BY a.showonly, a.want_this_parameter_lastdate, b.lastdate
ORDER BY start ASC;

after I query I get this

showonly|want_this_parameter|start  |stop   |lastdate
a1      |b1*                |5/7/19 |6/7/19 |(null)
a2      |b2**               |6/7/19 |7/7/19 |(null)
a3      |b1*                |7/7/19 |8/7/19 |(null)
a4      |b2**               |9/7/19 |10/7/19|(null)
a5      |b4                 |10/7/19|11/7/19|(null)

I have expect this (stop is last b1 used in a1 , lastdate is last b1 used in all table)

showonly|want_this_parameter|start  |stop   |lastdate
a1      |b1*                |5/7/19 |6/7/19 |12/17/19--Last B1 used
a2      |b2**               |6/7/19 |7/7/19 |11/17/19--Last B2 used
a3      |b1*                |7/7/19 |8/7/19 |12/17/19--Last B1 used
a4      |b2**               |9/7/19 |10/7/19|12/17/19--Last B2 used
a5      |b4                 |10/7/19|11/7/19|12/17/19--Last B4 used

*Same b1
**Same b2

UPDATE I finally get this but still concern about time to query performance because MAX(…) will fetch entire table right? How can I improve this performance.

SELECT a.showonly
     , a.want_this_parameter_lastdate
     , min(a.xdate) start
     , max(a.xdate) stop
     , b.lastdate
  FROM samedatabase a 
       LEFT JOIN (SELECT MAX(xdate) lastdate, want_this_parameter_lastdate 
                    FROM samedatabase 
                  GROUP BY want_this_parameter_lastdate) b
              ON a.want_this_parameter_lastdate = b.want_this_parameter_lastdate
 WHERE something IN (SELECT DISTINCT equalsomething 
                       FROM another 
                      WHERE input like '...') 
   AND xdate> sysdate - 7
GROUP BY a.showonly, a.want_this_parameter_lastdate, b.lastdate
ORDER BY start ASC;

Answer

I think that simple subquery would make the job faster, this way you eliminate double grouping, which is not neccesary:

select showonly, parameter, min(xdate) min_dt, max(xdate) max_dt,
       (select max(xdate) from samedatabase where parameter = s1.parameter) lst_dt
  from samedatabase s1
  where xdate > sysdate - 7
    and something in (select equalsomething from another where input like 'i%')
  group by showonly, parameter

^{dbfiddle demo}

Advertisement

Answer