Oracle sql distinct query

Question

I need to retrieve the distinct values of both the column and row. The oracle sql query in the current setup is given below:

select distinct ym.wh_id,
ym.trlr_num,
ym.arrdte,
ri.invnum,
ri.supnum
from rcvinv ri, yms_ymr ym
where ym.trlr_cod='RCV'
and ri.trknum = ym.trlr_num
and ym.wh_id <=50
and ym.trlr_stat in ('C','CI','R','OR')
and ym.arrdte is not null
order by ym.arrdte desc; 

The above returns the output as follows:

> Trailer Number        Arrived     PO              Vendor
> Trailer4              5/12/2015   010025790692    00101
> Trailer5-V6661        5/12/2015   010025754823    00110
> Trailer2-V6651        5/12/2015   010025781421    55395
> TRAILERS1-V6641       5/12/2015   010025790388    00915
> DEV110501-V6631       5/11/2015   010025790692    00101
> Rj-V6621              5/11/2015   010025790692    00101
> 12345-V6601           5/8/2015    010025751682    00128
> 12345-V6601           5/8/2015    010025754823    00110

I require the output as follows:

> Trailer Number        Arrived     PO              Vendor
> Trailer4              5/12/2015   010025790692    00101
> Trailer5-V6661        5/12/2015   010025754823    00110
> Trailer2-V6651        5/12/2015   010025781421    55395
> TRAILERS1-V6641       5/12/2015   010025790388    00915
> 12345-V6601           5/8/2015    010025751682    00128

As you can see, the repeated outputs for PO (010025790692 and 010025754823) and trailer number(12345-V6601) have been removed.

So in short, I want to modify the query such that I get the distinct of both the row and the column as in the below output. Please help. Thanks.

Answer 1

It shows that the main problem here is not how to write the query, but what query to write. First of all the expression "distinct values of both the column and row" doesn't make much sense. What I noticed was that you were showing distinct POs with the latest trailer and wrote my other answer accordingly. But obviously this is not what you really had in mind.

From your different comments I gather this: You want to show all lines for which no later trailer (trlr_num) with the same PO (invnum) and no higher vendor (supnum) for the same trailer (trlr_num) exists. This means two NOT EXISTS clauses. If this is actually what you want, then your query is:

with myquery as
(
  select ym.wh_id, ym.trlr_num, ym.arrdte, ri.invnum, ri.supnum
  from rcvinv ri
  join yms_ymr ym on ri.trknum = ym.trlr_num
  where ym.trlr_cod = 'RCV'
  and ym.wh_id <= 50
  and ym.trlr_stat in ('C','CI','R','OR')
  and ym.arrdte is not null
)
select *
from myquery
where not exists
(
  select *
  from myquery later_trailer
  where later_trailer.invnum = myquery.invnum
  and later_trailer.arrdte > myquery.arrdte
)
and not exists
(
  select *
  from myquery higher_vendor
  where higher_vendor.trlr_num = myquery.trlr_num
  and higher_vendor.supnum > myquery.supnum
);

Answer 2

You could use the Analytic ROW_NUMBER(). See the SQL Fiddle.

For example,

SQL> SELECT trailer_number,
  2    po,
  3    vendor
  4  FROM
  5    (SELECT t.*,
  6      row_number() OVER(PARTITION BY po, vendor ORDER BY po, vendor) rn
  7    FROM t
  8    )
  9  WHERE rn = 1;

TRAILER_NUMBER                    PO               VENDOR
--------------- -------------------- --------------------
12345-V6601              10025751682                  128
Trailer5-V6661           10025754823                  110
Trailer2-V6651           10025781421                55395
TRAILERS1-V6641          10025790388                  915
Trailer4                 10025790692                  101

SQL>

Update OP wants to know how to apply the analytic function on his original query:

Your modified query would look like:

WITH t AS
  (SELECT DISTINCT ym.trlr_num trlr_num,
    ym.arrdte arrdte,
    ri.invnum invnum,
    ri.supnum supnum
  FROM rcvinv ri,
    yms_ymr ym
  WHERE ym.trlr_cod ='RCV'
  AND ri.trknum     = ym.trlr_num
  AND ym.wh_id     <=50
  AND ym.trlr_stat IN ('C','CI','R','OR')
  AND ym.arrdte    IS NOT NULL
  ),
  t1 AS (
  SELECT t.trlr_num,
  t.arrdte,
  t.invnum,
  t.supnum,
  row_number() OVER (PARTITION BY t.trlr_num, t.invnum ORDER BY t.trlr_num, t.invnum DESC) rn
  FROM t
  )
SELECT trlr_num, arrdte, invnum, supnum 
   FROM t1 
  WHERE rn = 1; 

The WITH clause would be resolved as a temporary table, so you need not create any static table.

Answer 3

Your request can be written as: Get me the latest record per invnum. You get this by numbering (i.e. using ROW_NUMBER) the rows per invnum (i.e. PARTITON BY invnum) in the order desired, such that the latest record gets #1 (ORDER BY ym.arrdte DESC). Once the numbering is done, you remove all undesired records, i.e. those with a number other then 1.

BTW: Don't use implicit comma-separate joins any longer. They were replaced by explicit joins more than twenty years ago for good reasons.

select wh_id, trlr_num, arrdte, invnum, supnum,
from
(
  select 
    ym.wh_id, ym.trlr_num, ym.arrdte, ri.invnum, ri.supnum,
    row_number() over (partition by ri.invnum order by ym.arrdte desc) as rn 
  from rcvinv ri
  join yms_ymr ym on ri.trknum = ym.trlr_num
  where ym.trlr_cod = 'RCV'
  and ym.wh_id <= 50
  and ym.trlr_stat in ('C','CI','R','OR')
  and ym.arrdte is not null
)
where rn = 1
order by arrdte desc, trlr_num;