How to get unique value in Oracle-CodePudding

My Query:

select unique f.documentname,f.projectname,f.documentdescription,
f.P0BUSINESSUNIT,f.P0ZONENAME,f.P0REGIONNAME,f.P0GROUPNAME,l.processname,
f.ORIGINALCREATIONDATE 
from p0findoc f, lcstep@sdrcpadm l 
where f.inlifecyclename IS Not Null and 
f.INLIFECYCLESTEP in (select l.LIFECYCLESTEP  from lcstep@sdrcpadm ) and ORIGINALCREATIONDATE > '2021/01/01' 
order by f.P0BUSINESSUNIT,f.P0ZONENAME,f.P0REGIONNAME,f.P0GROUPNAME ;

Output:

DocumentName, Project Name, Doc Description...so on

PD71017 311503  Change Order 1  CIS Americas CIS    Brazil CIS  Not Applicable  Deliver Services    2021/05/04-14:50:07:998

PD71017 311503  Change Order 1  CIS Americas CIS    Brazil CIS  Not Applicable  Notification    2021/05/04-14:50:07:998

PD71017 311503  Change Order 1  CIS Americas CIS    Brazil CIS  Not Applicable  PRJ Notification    2021/05/04-14:50:07:998

PD71017 311503  Change Order 1  CIS Americas CIS    Brazil CIS  Not Applicable  Request CSS Closure 2021/05/04-14:50:07:998

PD71017 311503  Change Order 1  CIS Americas CIS    Brazil CIS  Not Applicable  Validation Failure  2021/05/04-14:50:07:998

PD73834 311503  Change Order 4  CIS Americas CIS    Brazil CIS  Not Applicable  Deliver Services    2021/09/13-17:24:18:433

PD73834 311503  Change Order 4  CIS Americas CIS    Brazil CIS  Not Applicable  Notification    2021/09/13-17:24:18:433

Here in above output i want to have unique value only for column 'document name' that is PDxxxx. there are multiple values for column process name so wanted to have just one.

CodePudding user response：

If you want one (any) process name, then take e.g. "first" or "last" (min or max), but you'll have to aggregate on all other columns (which also means that you don't need unique any more):

  SELECT f.documentname,
         f.projectname,
         f.documentdescription,
         f.p0businessunit,
         f.p0zonename,
         f.p0regionname,
         f.p0groupname,
         MAX (l.processname) processname,
         f.originalcreationdate
    FROM p0findoc f
         JOIN lcstep@sdrcpadm l ON f.inlifecyclestep = l.lifecyclestep
   WHERE     f.inlifecyclename IS NOT NULL
         AND originalcreationdate > DATE '2021-01-01'
GROUP BY f.documentname,
         f.projectname,
         f.documentdescription,
         f.p0businessunit,
         f.p0zonename,
         f.p0regionname,
         f.p0groupname,
         f.originalcreationdate
ORDER BY f.p0businessunit,
         f.p0zonename,
         f.p0regionname,
         f.p0groupname;

Also, you'd rather not compare dates to strings; that's why I used date literal in where clause. Finally, switched to JOIN.

CodePudding user response：

I think you're getting the wrong answer because you did not join your two tables together with any kind of a unique key.

Change your FROM clause to From p0findoc f Inner Join lcstep@sdrcpadm l on f.InLifecycleStep=l.LifecycleStep

and remove the subquery f.INLIFECYCLESTEP in (select l.LIFECYCLESTEP from lcstep@sdrcpadm )