Is there a way to distinct multiple columns in sql?

Is there a way to distinct multiple columns? When I tried to do it with p.name it says that there is an error that occurred.

SELECT DISTINCT( V.NAME ), 
               POH.status, 
               poh.shipdate, 
               pod.orderqty, 
               POD.receivedqty, 
               POD.rejectedqty, 
               p.NAME 
FROM   purchasing.vendor v 
       INNER JOIN purchasing.productvendor pv 
               ON v.businessentityid = pv.businessentityid 
       INNER JOIN production.product p 
               ON pv.productid = P.productid 
       INNER JOIN purchasing.purchaseorderdetail POD 
               ON P.productid = POD.productid 
       INNER JOIN purchasing.purchaseorderheader POH 
               ON POD.purchaseorderid = POH.purchaseorderid 
ORDER  BY v.NAME, 
          p.NAME;

Answer

If you want one row per NAME, then you can use ROW_NUMBER():

with q as (
      <your query here with columns renamed so there are no duplicates>
     )
select q.*
from (select q.*,
             row_number() over (partition by v_name order by v_name) as seqnum
      from q
     ) q
where seqnum = 1;

Advertisement

Answer