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