CODEWITHSUNDEEP
sqloracle-databasegroup-bydistinct
Matthew Paxman
Matthew Paxman

Reputation: 247

Return single row for each value in specific column

I know this is probably a neggable question, but no matter what logic I'm using around GROUP BY and/or DISTINCT, I can't seem to get this to work. What I need is one row returned for each CODE, where it's the most recent CREATED_DATE in the last month. There can be multiple CREATED_DATEs for each CODE within the month. As I said, it's likely some use of DISTINCT or GROUPING, but I can't seem to get anything to work. Maybe it's just early =/

SELECT PRJ.CODE, 
                 PRJ.ID, 
                 RAGRPT.CREATED_DATE, 
                 RAGRPT.NAME,
                 RAGRPT.COP_REPORT_UPDATE OVERALL_HEALTH_COMMENT,
                  (CASE
                        WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 40 AND 50 THEN 'GREEN'
                        WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 60 AND 70 THEN 'YELLOW'
                        WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 80 AND 120 THEN 'RED'
                        ELSE ' '
                  END) OVERALL_STATUS,
                  RAGRPT.COP_SCHEDULE_EXP SCHEDULE_EXPLANATION,
                  (CASE 
                        WHEN RAGRPT.COP_SCHEDULE_STATUS = 10 THEN 'GREEN'
                        WHEN RAGRPT.COP_SCHEDULE_STATUS = 20 THEN 'YELLOW'
                        WHEN RAGRPT.COP_SCHEDULE_STATUS = 30 THEN 'RED'
                        ELSE ' '
                  END) SCHEDULE_STATUS,
                  RAGRPT.COP_SCOPE_EXP SCOPE_EXPLANATION,
                  (CASE 
                        WHEN RAGRPT.COP_SCOPE_STATUS = 10 THEN 'GREEN'
                        WHEN RAGRPT.COP_SCOPE_STATUS = 20 THEN 'YELLOW'
                        WHEN RAGRPT.COP_SCOPE_STATUS = 30 THEN 'RED'
                        ELSE ' '
                  END) SCOPE_STATUS,
                  RAGRPT.COP_EFFORT_EXP COST_EXPLANATION,
                  (CASE 
                        WHEN RAGRPT.COP_COST_EFT_STATUS = 10 THEN 'GREEN'
                        WHEN RAGRPT.COP_COST_EFT_STATUS = 20 THEN 'YELLOW'
                        WHEN RAGRPT.COP_COST_EFT_STATUS = 30 THEN 'RED'
                        ELSE ' '
                  END) COST_STATUS,
                  RAGRPT.RMS_RESOURCE_EXP RESOURCE_EXPLANATION,
                  (CASE 
                        WHEN RAGRPT.RMS_RESOURCE_STATUS = 10 THEN 'GREEN'
                        WHEN RAGRPT.RMS_RESOURCE_STATUS = 20 THEN 'YELLOW'
                        WHEN RAGRPT.RMS_RESOURCE_STATUS = 30 THEN 'RED'
                        ELSE ' '
                  END) RESOURCE_STATUS

    FROM  ODF_CA_COP_PRJ_STATUSRPT RAGRPT, INV_INVESTMENTS PRJ
    WHERE PRJ.ID = RAGRPT.ODF_PARENT_ID (+)
          AND RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE
    ORDER BY PRJ.CODE, RAGRPT.CREATED_DATE DESC

Upvotes: 0

Views: 92

Answers (2)

PM 77-1
PM 77-1

Reputation: 13352

And my example will be with GROUP BY:

SELECT code, MAX(thedate) 
FROM Table1
WHERE thedate between add_months(trunc(sysdate,'mm'),-1) and
      last_day(add_months(trunc(sysdate,'mm'),-1))
GROUP BY code

SQL Fiddle

I understood last month as the previous calendar monthand shamelessly borrowed Oracle code from this answer.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You don't want a group by. You want to use row_number(). Here is the structure of the query:

SELECT *
FROM (SELECT <whatever>,
             ROW_NUMBER() OVER (PARTITION BY PRJ_CODE ORDER BY RAGRPT.CREATED_DATE DESC) as seqnum
      FROM  INV_INVESTMENTS PRJ LEFT JOIN
            ODF_CA_COP_PRJ_STATUSRPT RAGRPT, 
            ON PRJ.ID = RAGRPT.ODF_PARENT_ID
      WHERE RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE
     ) t
WHERE seqnum = 1;

Upvotes: 1

Related Questions