How to return only unique rows from SQL Query that already uses distinct

I know that you can return only unique rows using distinct operator on a specific column. I successfully use the distinct operator on a column for a query. Now, I would like the query to only return distinct results of the remaining columns. For example the query below:

Select distinct pl.oid, pr.npd, sch.shortstringvalue
from sonpda_rdb.jpipelinesystem pl
join sonpda_rdb.xsystemhierarchy xsys1 on xsys1.oidorigin = pl.oid
join sonpda_rdb.jnameditem it1 on it1.oid = pl.oid
join sonpda_rdb.jrtepiperun pr on pr.oid = xsys1.oiddestination
join sonpda_rdb.jnameditem it2 on it2.oid = pr.oid
join sonpda_rdb.xownsparts xop on xop.oidorigin = pr.oid
join sonpda_rdb.jrtestockpartoccur stckprt on stckprt.oid = xop.oiddestination
join sonpda_rdb.xmadefrom xmf on xmf.oidorigin = stckprt.oid
join sonpda_rdb.jdpipecomponent pipcmp on pipcmp.oid = xmf.oiddestination
join sonpda_rdb.cl_schedulethickness sch on sch.valueid = pipcmp.firstsizeschedule
where it1.itemname = 'C-8001'
order by pr.npd desc;

Gives the results shown in table below

00033457000000000625CE8F235CF10F 2.0 S-40S

000334570000000012BADECCFA5B4804 2.0 S-40S

00033457000000000625CE8F235CF10F 1.0 S-40S

000334570000000012BADECCFA5B4804 1.0 S-40S

000334570000000014BAB9B0FB5B7704 1.0 S-40S

There are only two distinct rows based on the second and third columns of the query. How can I modify the query to only return these two distinct rows?

Answer

One method uses row_number():

with q as (
      <your query here>
     )
select q.*
from (select q.*,
             row_number() over (partition by npd, shortstringvalue order by oid desc) as seqnum
      from q
     ) q
where seqnum = 1;

Since Oracle 12C, you can also do this using:

select . . .
from . . .
order by row_number() over (partition by npd, shortstringvalue order by oid desc)
fetch first 1 row with ties;

Advertisement

Answer