CODEWITHSUNDEEP
sqloracle-databasegroup-bydistinct
Reeya Oberoi
Reeya Oberoi

Reputation: 859

DISTINCT is returning duplicate rows in Oracle

I am not a SQL pro. I used all columns in my SELECT statement and used them all in GROUP BY too. However, I was getting duplicate records. Then I added DISTINCT to my query but still I am getting duplicate records. What am I doing wrong here?

SELECT DISTINCT 
        action_by_id
       ,a.status_cd
       ,owner_dsply_nm                      AS "CASE OWNER"
       ,case_intrl_id                       AS "CASE ID"
       ,status_nm                           AS "STATUS VALUE"
       ,action_ts                           AS "STATUS CHANGE DATE"
       ,To_char(action_ts - 7 / 24, 'IYYY') AS "STATUS CHANGE YEAR" 
       ,To_char(action_ts - 7 / 24, 'IW')   AS "STATUS CHANGE WEEK" 
FROM   kdd_case_actions a 
       join kdd_status  s 
         ON a.status_cd = s.status_cd 
       join kdd_review_owner r
         ON r.owner_seq_id = a.action_by_id
WHERE  a.status_cd NOT IN ( 'SBTMNEW', 'NW', 'SB01NEW' ) 
GROUP  BY To_char(action_ts - 7 / 24, 'IYYY') 
          ,To_char(action_ts - 7 / 24, 'IW') 
          ,action_ts
          ,case_intrl_id 
          ,status_nm
          ,owner_dsply_nm
          ,action_by_id
          ,a.status_cd

Results

Upvotes: 1

Views: 3828

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

There is not aggregation function in your query so why are using group by .. use only distinct
and be sure that you get only the date parte for action_ts and last be sure you have proper trimmed string for space or not visible char 

    SELECT DISTINCT 
            action_by_id
           ,trim(a.status_cd)
           ,trim(owner_dsply_nm)                      AS "CASE OWNER"
           ,case_intrl_id                       AS "CASE ID"
           ,trim(status_nm )                         AS "STATUS VALUE"
           ,to_char(action_ts, 'DD.MON.YYYY')    AS "STATUS CHANGE DATE"
           ,To_char(action_ts - 7 / 24, 'IYYY') AS "STATUS CHANGE YEAR" 
           ,To_char(action_ts - 7 / 24, 'IW')   AS "STATUS CHANGE WEEK" 
    FROM   kdd_case_actions a 
    join kdd_status  s ON a.status_cd = s.status_cd 
    join kdd_review_owner r ON r.owner_seq_id = a.action_by_id
    WHERE  a.status_cd NOT IN ( 'SBTMNEW', 'NW', 'SB01NEW' ) 


    TO_DATE (action_ts, 'DD.MON.YYYY')

Upvotes: 1

Kristian Saksen
Kristian Saksen

Reputation: 114

If your action_ts column is a timestamp or a date your only seeing a part of it in your user interface. I guess you will see that the rows have different values on that column if you print it out more detailed.

Upvotes: 2

Related Questions