Need multiple maxdates-oracle sql developer 4.0.2

Question

Below is the query I'm working with - I need to get the MAX date for each of the PhaseEndDt. I've tried the

(SELECT Max(v) FROM (VALUES (aphase1.updated_ts), (aphase2.updated_ts), (aphase3.updated_ts)) AS VALUE(v)) AS [MaxDate]

but it isn't working :-( any help would be greatly appreciated!

SELECT
dc.case_id,
aphase1.identifier_value AS "phase1",
aphase1.updated_ts AS "phase1_enddt",
aphase2.identifier_value AS "phase2",
aphase2.updated_ts AS "phase2_enddt",
aphase3.identifier_value AS "phase3",
APHASE3.UPDATED_TS AS "Phase3_EndDt"
FROM cmreporting.d_solution ds
INNER JOIN cmreporting.d_case dc ON ds.solution_sqn = dc.solution_sqn
LEFT JOIN cmreporting.a_identifiers aphase1 ON aphase1.identifier_value = 'Phase 1' AND dc.case_ID = aphase1.group_ID
LEFT JOIN cmreporting.a_identifiers aphase2 ON aphase2.identifier_value = 'Phase 2' AND dc.case_ID = aphase2.group_ID
LEFT JOIN cmreporting.a_identifiers aphase3 ON aphase3.identifier_value = 'Phase 3' AND dc.case_ID = aphase3.group_ID

See this question which has the same scenario: http://stackoverflow.com/questions/4903527/how-to-get-the-max-row-number-per-group-partition-in-sql-server — Tarzan, Apr 05 '16 at 19:54
I found method that you try use here: http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns . This is for tsql... — Adam Silenko, Apr 05 '16 at 21:41
I need to select the max aphase1.updated_ts in it's column, aphase2.updated_ts in it's column, and aphase3.updated_ts in it's column, independent of each other. The case date 1>= date 2 is not the solution I'm looking. The Row_Number solution will not let me filter WHERE row 1 = 1, row 2 = 1, row 3 = 1. — Fhaynes, Apr 06 '16 at 17:14

Adam Silenko · Answer 1 · 2016-04-05T21:46:22.607

if you only need max of updated_ts you can use subquery or case:

SELECT
dc.case_id,
aphase1.identifier_value AS "phase1",
aphase1.updated_ts AS "phase1_enddt",
aphase2.identifier_value AS "phase2",
aphase2.updated_ts AS "phase2_enddt",
aphase3.identifier_value AS "phase3",
aphase3.updated_ts AS "phase3_enddt",
(SELECT MAX(updated_ts) FROM cmreporting.a_identifiers WHERE group_ID = dc.case_ID AND identifier_value IN('Phase 1', 'Phase 2', 'Phase 3')) AS max_updated_ts_subquery,
CASE WHEN aphase1.updated_ts > aphase2.updated_ts AND aphase1.updated_ts > aphase3.updated_ts THEN aphase1.updated
     WHEN aphase2.updated_ts > aphase3.updated_ts THEN aphase2.updated_ts
     ELSE aphase3.updated_ts END AS max_updated_ts_case,
(SELECT MAX(col) FROM (VALUES(aphase1.updated_ts), (aphase2.updated_ts), (aphase3.updated_ts)) AS tab(col)) AS max_updated_ts_from_values
FROM cmreporting.d_solution ds
INNER JOIN cmreporting.d_case dc ON ds.solution_sqn = dc.solution_sqn
LEFT JOIN cmreporting.a_identifiers aphase1 ON aphase1.identifier_value = 'Phase 1' AND dc.case_ID = aphase1.group_ID
LEFT JOIN cmreporting.a_identifiers aphase2 ON aphase2.identifier_value = 'Phase 2' AND dc.case_ID = aphase2.group_ID
LEFT JOIN cmreporting.a_identifiers aphase3 ON aphase3.identifier_value = 'Phase 3' AND dc.case_ID = aphase3.group_ID

EDIT: but for oracle use GREATEST like this:

SELECT
dc.case_id,
aphase1.identifier_value AS "phase1",
aphase1.updated_ts AS "phase1_enddt",
aphase2.identifier_value AS "phase2",
aphase2.updated_ts AS "phase2_enddt",
aphase3.identifier_value AS "phase3",
aphase3.updated_ts AS "phase3_enddt",
GREATEST(aphase1.updated_ts, aphase2.updated_ts, aphase3.updated_ts) AS MaxDate
FROM cmreporting.d_solution ds
INNER JOIN cmreporting.d_case dc ON ds.solution_sqn = dc.solution_sqn
LEFT JOIN cmreporting.a_identifiers aphase1 ON aphase1.identifier_value = 'Phase 1' AND dc.case_ID = aphase1.group_ID
LEFT JOIN cmreporting.a_identifiers aphase2 ON aphase2.identifier_value = 'Phase 2' AND dc.case_ID = aphase2.group_ID
LEFT JOIN cmreporting.a_identifiers aphase3 ON aphase3.identifier_value = 'Phase 3' AND dc.case_ID = aphase3.group_ID

It doesn't like the VALUES from the (SELECT MAX(col) FROM (VALUES(aphase1.updated_ts.... part. Tried using Greatest, but it just returns the greatest date from the a_identifiers Updated_ts field. — Fhaynes, Apr 06 '16 at 15:54
I don't understand you. Greatest work exactly like MAX FROM VALUES in tsql. How you test it? Can you post your query of this test? — Adam Silenko, Apr 06 '16 at 16:17

score 0 · Answer 2 · answered Apr 07 '16 at 12:34

In Case any one else out there has this same issue, this is what I was finally able to get to work!

WITH CTE_Result AS(
SELECT 
DC.CASE_ID, 
APHASE1.IDENTIFIER_VALUE AS "Phase1", 
APHASE1.UPDATED_TS AS "Phase1Enddt",
APHASE2.identifier_value AS "Phase2",
APHASE2.UPDATED_TS AS "Phase2EndDt",
APHASE3.identifier_value AS "Phase3",
APHASE3.UPDATED_TS AS "Phase3EndDt",
ROW_NUMBER() OVER(PARTITION BY DC.CASE_ID ORDER BY APHASE1.UPDATED_TS DESC, APHASE2.UPDATED_TS DESC, APHASE3.UPDATED_TS DESC) AS Row_id 

FROM CMREPORTING.D_SOLUTION DS
INNER JOIN CMREPORTING.D_CASE DC ON DS.SOLUTION_SQN = DC.SOLUTION_SQN
LEFT OUTER JOIN CMREPORTING.A_IDENTIFIERS APHASE1 on APHASE1.identifier_value = 'Phase 1' and dc.case_ID = APHASE1.group_ID
LEFT OUTER JOIN CMREPORTING.A_IDENTIFIERS APHASE2 on APHASE2.identifier_value = 'Phase 2' and dc.case_ID = APHASE2.group_ID
LEFT OUTER JOIN CMREPORTING.A_IDENTIFIERS APHASE3 on APHASE3.identifier_value = 'Phase 3' and dc.case_ID = APHASE3.group_ID

WHERE 
DC.CASE_ID = '{091225F8-4606-401C-872E-FC5ACDC1D8E2}' 
)
SELECT *FROM CTE_Result WHERE Row_id=1

Adam Silenko · Accepted Answer · 2016-04-12T20:34:29.453

you can do it much easier, and probably faster:

WITH
  parms as (select 'Phase 1' AS "Phase1", 'Phase 2' AS "Phase2", 'Phase 3' AS "Phase3", '{091225F8-4606-401C-872E-FC5ACDC1D8E2}' AS case_id from dual)
SELECT 
dc.case_id, 
parms."Phase1", 
SELECT Max(updated_ts) FROM a_identifiers WHERE identifier_value = parms."Phase1" AND group_ID = dc.case_ID) AS "Phase1Enddt",
parms."Phase2",
SELECT Max(updated_ts) FROM a_identifiers WHERE identifier_value = parms."Phase2" AND group_ID = dc.case_ID) AS "Phase2Enddt",
parms."Phase3",
SELECT Max(updated_ts) FROM a_identifiers WHERE identifier_value = parms."Phase3" AND group_ID = dc.case_ID) AS "Phase3Enddt",
FROM parms, cmreporting.d_solution ds
INNER JOIN cmreporting.d_case dc ON ds.solution_sqn = dc.solution_sqn
WHERE dc.case_id = parms.case_id 
AND rownum = 1 --if ther is more then one row

EDIT:
the same result you can get by this query:

WITH
  parms as (select 'Phase 1' AS "Phase1", 'Phase 2' AS "Phase2", 'Phase 3' AS "Phase3", '{091225F8-4606-401C-872E-FC5ACDC1D8E2}' AS case_id from dual)
SELECT 
group_ID as case_id, 
parms."Phase1", 
Max(Case When identifier_value = parms."Phase1" Then updated_ts End) AS "Phase1Enddt",
parms."Phase2",
Max(Case When identifier_value = parms."Phase2" Then updated_ts End) AS "Phase2Enddt",
parms."Phase3",
Max(Case When identifier_value = parms."Phase3" Then updated_ts End) AS "Phase3Enddt",
FROM parms, a_identifiers
Where Exists (Select 1 From cmreporting.d_solution ds
  INNER JOIN cmreporting.d_case dc ON ds.solution_sqn = dc.solution_sqn
  WHERE dc.case_id = a_identifiers.group_ID)
AND group_ID = parms.case_ID
GROUP BY group_ID

This was so much faster! My full query is about 115 rows, and needed the max for about 10 fields - took it from 7min to under 2min! thanks you Adam!!! — Fhaynes, Apr 12 '16 at 19:19

Need multiple maxdates-oracle sql developer 4.0.2

3 Answers3