I am trying to run a query in Oracle where I want to do a distinct select on 2 columns (position, at.task_number) but overall I still want to display an additional 4 columns.

I understand that Oracle just uses distinct on all columns used and not just the order clause anyway. If I remove the qualifications of the APP_GRP and ag.app_group_cd then my query gives me the output I want but I also need to display those columns.

Here's how my select looks today but it's not giving me what I want.

select distinct
(select cv.display from code_value cv where cv.code_value = ag.position_cd) AS POSITION, AG.POSITION_CD,
(select cv.display from code_value cv where cv.code_value = ag.app_group_cd) AS APP_GRP, ag.app_group_cd, 
at.task_number, at.description, Users=count(ag.position_cd)
from
application_group ag, application_task at, task_access ta, code_value cv
plan cv
where cv.code_set= 78
and cv.active_ind = 1
and cv.begin_effective_dt_tm <= SYSDATE
and cv.end_effective_dt_tm >= SYSDATE
join ag
where ag.position_cd = cv.code_value
join ta
where ta.app_group_cd = ag.app_group_cd
join at
where at.task_number = ta.task_number
and at.task_number in (1, 2, 3, 4, 5, 6)
and at.active_ind = 1
join d
group by
ag.position_cd, ag.app_group_cd, at.task_number, at.description
having
COUNT(ag.position_cd)&g开发者_开发知识库t;0
order by position, at.task_number

UPDATED

My query is returning something like this...

POSITION-----APP_GRP-----TASK_NUMBER

zzzDBA-------------DBA---------------------1

zzzDBA-------------DBA---------------------4

zzzDBA-------------DBA---------------------3

zzzDBA-------------DBA---------------------5

zzzDBA-------------DBA---------------------2

zzzDBA-------------DBA--------------------3

zzzDBA-------------DBA--------------------4

zzzDBA-------------DBA--------------------5

Ultimately I want to get to where I only return:

POSITION-----APP_GRP-----TASK_NUMBER

zzzDBA-------------DBA---------------------1

zzzDBA-------------DBA---------------------4

zzzDBA-------------DBA---------------------3

zzzDBA-------------DBA---------------------5

zzzDBA-------------DBA---------------------2

"removing" the dup rows for TASK_NUMBER IN (3, 4 5)

---

If you are not getting distinct values for those two fields then it means you have multiple values being returned in the APP_GRP and ag.app_group_cd fields.

If you apply something like MAX() to these fields and remove ag.app_group_cd from your GROUP BY statement, you will probably get distinct values, but you are essentially hiding the fact that the combination yields multiple values for APP_GRP and ag.app_group_cd.

If this doesn;t make sense, it would be easier to see example query results for what you expect to see, and what your query is returning.

---

This query seemed to do the trick...

SELECT      tn.Position,
tn.Task_Number,
tn.TotalPosCd,
maxapp.MaxAppGroupCD,
ta.Description,
appgrp.display AS APP_GRP

FROM
        (SELECT     cv.display AS Position,
                    at.Task_Number,
                    COUNT(ag.position_code) AS TotalPosCd
        FROM  APPLICATION_TASK AT ,
                    TASK_ACCESS TA ,
                    APPLICATION_GROUP AG ,
                    CODE_VALUE CV
        WHERE AG.POSITION_CD  =    CV.CODE_VALUE 
                    AND TA.APP_GROUP_CD = AG.APP_GROUP_CD 
                    AND AT.TASK_NUMBER =  TA.TASK_NUMBER 
                    AND CV.CODE_SET =  88
                    AND CV.ACTIVE_IND =  1
                    AND CV.BEGIN_EFFECTIVE_DT_TM <= SYSDATE
                    AND CV.END_EFFECTIVE_DT_TM >=  SYSDATE
                    AND AT.TASK_NUMBER IN (1,2,3,4,5,6)
                    AND AT.ACTIVE_IND = 1 
        GROUP BY    cv.display,
                          at.Task_Number
        HAVING      COUNT(ag.Position_code) > 0
        ) tn,
        (SELECT ta.Task_Number,
                    MAX(ag.App_Group_CD) AS MaxAppGroupCD
        FROM  TASK_ACCESS ta,
                    APPLICATION_TASK at,
                    APPLICATION_GROUP ag
        WHERE tn.Task_number = ta.Task_number
                    AND ta.Task_Number = at.Task_number
                    AND ta.App_Group_CD = ag.App_Group_CD
        GROUP BY ta.Task_number
        ) maxapp,
        TASK_ACCESS ta,
        CODE_VALUE appgrp

WHERE tn.Task_number = maxapp.Task_Number
        AND maxapp.MaxAppGroupCD = ta.app_group_cd
        AND tn.Task_Number = ta.Task_Number
        AND maxapp.MaxAppGroupCD = appgrp.Code_Value