How to select a unique row derived of multiple max(case when) without using distinct in Oracle SQL

Question

I need to select the most recent upload date for different documents per id and I would like there to only be one row per id.

What I am currently doing:

SELECT DISTINCT id,
                tree_date,
                sun_date
FROM
  (SELECT id,
          max(CASE
                  WHEN doc_name LIKE 'tree%' THEN upload_date 
                            END) OVER (PARTITION BY id ORDER BY id) tree_date,
          max(CASE WHEN doc_name LIKE 'sun%' THEN upload_date
                            END) OVER (PARTITION BY id ORDER BY id) sun_date
   FROM documents
   WHERE doc_name LIKE 'tree%'
     OR doc_name LIKE 'sun%' )

This query works and gives me my desired results, but I do not believe I am selecting the max upload date of a document per id in the most efficient manner. Can anyone suggest a more efficient way preferable without distinct as I believe this slows down the query dramatically.

Any help is appreciated. Please let me know if more information is needed.

Answer 1

Do you simply want conditional aggregation?

SELECT id,
       MAX(CASE WHEN doc_name LIKE 'tree%' THEN upload_date END) as tree_date,
       MAX(CASE WHEN doc_name LIKE 'sun%' THEN upload_date END) as sun_date
FROM documents
WHERE doc_name LIKE 'tree%' OR
      doc_name LIKE 'sun%'
GROUP BY id;