with CTE statement giving error

ArtieLadie — Tue, 14 May 2019 16:37:05 GMT

I am pulling query from Oracle Database into Power BI that outputs this

but I want it to output this

I thought MIN(I.CREATIONDATE) would give date-time with earliest hour in my original query

SELECT TO_CHAR(MIN(I.INCIDENTID))               AS "Incident ID",
          MIN(I.CREATIONDATE)            AS "Creation Date",
          TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY')            AS "Date",
          TRIM(MO.DOMAINUSERNAME)               AS "Login ID",  
          TRIM(M.MESSAGESUBJECT)                AS "Email Subject"
FROM   MESSAGE M 
JOIN   INCIDENT I 
ON     M.MESSAGESOURCE = I.MESSAGESOURCE 
AND       M.MESSAGEID = I.MESSAGEID
AND    M.MESSAGEDATE    = I.MESSAGEDATE 
JOIN   MESSAGEORIGINATOR MO
ON     M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID                  
GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'),
         TRIM(MO.DOMAINUSERNAME),
         TRIM(M.MESSAGESUBJECT)

Then I was advised to use CTE with row_number() but now I get

Message=ORA-00933: SQL command not properly ended

I tried many variations. How to fix this?

with CTE as (
SELECT MyTable.*, 
       row_number() over (PARTITION BY MIN(I.CREATIONDATE) ORDER BY I.CREATIONDATE) AS rn   
FROM MyTable
)
SELECT *
FROM CTE
WHERE rn = 1

SELECT TO_CHAR(MIN(I.INCIDENTID))               AS "Incident ID",
          MIN(I.CREATIONDATE)            AS "Creation Date",
          TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY')            AS "Date",
          TRIM(MO.DOMAINUSERNAME)               AS "Login ID",  
          TRIM(M.MESSAGESUBJECT)                AS "Email Subject"
FROM   MESSAGE M 
JOIN   INCIDENT I 
ON     M.MESSAGESOURCE = I.MESSAGESOURCE 
AND       M.MESSAGEID = I.MESSAGEID
AND    M.MESSAGEDATE    = I.MESSAGEDATE 
JOIN   MESSAGEORIGINATOR MO
ON     M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID                  
GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'),
         TRIM(MO.DOMAINUSERNAME),
         TRIM(M.MESSAGESUBJECT)

topic with CTE statement giving error in Report Server

with CTE statement giving error