min/max data in oracle sql in table in group-CodePudding

I have a table looking like:

Now I would like to get for each group the min and max dates based on the creation date column and the str_val column. So for the group_id 1 with object_id 1 must show 01-01-2010 and 25-04-2016 and group_id 2 and object_id 2 both 01-02-2001 for max and min. Can this be achieved using oracle sql?

I would like to get:

CodePudding user response：

If the whole data set is competent to the sample data set, then use such a query

SELECT group_id, object_id,
       MIN(LEAST(creation_date,
                 CASE WHEN LENGTH(TRIM(str_val))=10 AND INSTR(str_val,'-')>0 THEN 
                           TO_DATE(TRIM(str_val),'dd-mm-yyyy') 
                       END)) AS "MIN",
       MAX(GREATEST(creation_date,
                    CASE WHEN LENGTH(TRIM(str_val))=10 AND INSTR(str_val,'-')>0 THEN 
                              TO_DATE(TRIM(str_val),'dd-mm-yyyy') 
                          END)) AS "MAX"
  FROM t
 GROUP BY group_id, object_id
 ORDER BY group_id, object_id

presuming creation_date is of DATE type, and str_val is evidently of a string type.

CodePudding user response：

Then you could use group by:

SELECT GROUP_ID, OBJECT_ID, MIN(CREATION_DATE), MAX(CREATION_DATE)
FROM TABLE
GROUP BY GROUP_ID, OBJECT_ID