Return single row for each value in specific column

Question

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

score 1 · Accepted Answer · answered Sep 10 '14 at 00:17

1

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;

answered Sep 10 '14 at 00:17

Gordon Linoff

1,242,037
58
646
786

Thanks so much, that was perfect. I'd seen that before but I wasn't sure what it was used for. – Matthew Paxman Sep 10 '14 at 00:53

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

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.

edited May 23 '17 at 10:33

Community

1
1

answered Sep 10 '14 at 00:48

PM 77-1

12,933
21
68
111

Return single row for each value in specific column

2 Answers2