CODEWITHSUNDEEP
sqloracle-database
Ellis Nijland
Ellis Nijland

Reputation: 23

min/max data in oracle sql in table in group

I have a table looking like: enter image description here

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: enter image description here

Upvotes: 0

Views: 82

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

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.

Upvotes: 2

Cosmos Lee
Cosmos Lee

Reputation: 159

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

Upvotes: 0

Related Questions