2

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.

Improve this question
2
  • 1
    You need to decide on what basis the distinct values for a column are chosen - i.e. why do you choose "Trailer4" for PO "010025790692" and not "DEV110501-V6631"? Commented May 13, 2015 at 9:00
  • Hi Raad. The condition is that the latest trailer or top most is taken into consideration with a PO. So if many trailers have a single PO then the latest trailer number is considered. Similarly the case when there are multiple POs and a single trailer. The latest PO alone is shown and the rest are omitted out. Commented May 13, 2015 at 9:41

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you Lalit. Actually this query retrieves from the database onto a report and hence there might be more number of values which would be changing. Is there any way to modify the existing query without creating a table?
I created the table to show an example, you don't have to cretae any table, just use the query and use your tablename instead of "t".
Could you please assist me in creating a temporary table for the fields from the tables yms_ymr and rcvinv and then use that table in the sql query ie using the rownum() and partition by. I think then this query should retrieve correctly.
@Rajiv, See the update. You need not create an static table in database,WITH clause will act like a temporary table.
1

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;
Improve this answer

8 Comments

Thanks Thorsten. I tried using the query, but I am still getting many repeating values for trlr_num and invnum. I tried using distinct, but to no avail. Any thing else needs to be modified?
You are getting duplicate invnums? I don't see how this is possible. I use row_number per invnum, so there can be only one record numbered 1 per invnum.
You can still get duplicate trlr_nums, as we select the latest record per invnum, and such records could have the same trlr_num. If this is not desired, then you'd have to clarify what you actually want: one line per invnum? One line per trlr_num? Or each invnum and each trlr_num, but as few records as possible?
The actual issue is that there could be many PO numbers for a Trailer Number. Also, many Trailer numbers can contain a PO number. The reqmt. is that for a trailer number only the top most PO number alone needs to be displayed even if there are multiple PO nos. Similarly, only the top most trailer number needs to be displayed if it has many trailer numbers for a PO. As given in the question above, there were repeating values both for the trailer number and PO. Require it such that there are no repeating values whether for PO or trailer number or both in the entire table.
But you don't display the top most PO for DEV110501-V6631. You dont display that trailer at all. So when am I allowed to suppress a trailer or PO completely?
|
0

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
);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.