CODEWITHSUNDEEP
sqloracle-database
user2470781
user2470781

Reputation: 77

Oracle - return only the first row for each product

In the first SELECT statement, the report grabs inv details for all products with shipment activity. There is then a UNION that connect another SELECT statement to grab products without activity from the last calendar year.

However, the records that are returned in the second SELECT statement have multiple header_id’s and therefore multiple lines… instead of single lines like the first SELECT statement. Do you know how to pull only the first header_id of each record in the second SELECT statement?

Code and example result set below. In data, product #7 should only list the row for header_id 1372288 which is the last ID entered into the DB.

enter image description here

select 3 sort_key, header_Id,location_id,nlasinv.product,
                                   start_inv,produced produced_inv,stored,from_stock,shipped,
                                   (start_inv + produced + stored) - (from_stock + shipped) end_inv,nlas_ops_mtd_prodsize(111,nlasinv.product,'31-DEC-19'), nlas_ops_mtd_shipsize(111,nlasinv.product,'31-DEC-19'),nlas_ops_ytd_prodsize(111,nlasinv.product,'31-DEC-19'), nlas_ops_ytd_shipsize(111,nlasinv.product,'31-DEC-19')
                                   from nlas_header inv,
                                     nlas_inventory nlasinv
                                   where nlasinv.header_id = 1372168   
                                   and inv.id = nlasinv.header_id
    union 
    select distinct
       3 sort_key,header_Id,location_id,nlasinv.product,
       start_inv,produced produced_inv,stored,from_stock,shipped,
                                   (start_inv + produced + stored) - (from_stock + shipped) end_inv,nlas_ops_mtd_prodsize(111,nlasinv.product,'31-DEC-19'),
       nlas_ops_mtd_shipsize(111,nlasinv.product,'31-DEC-19'),nlas_ops_ytd_prodsize(111,nlasinv.product,'31-DEC-19'),
       nlas_ops_ytd_shipsize(111,nlasinv.product,'31-DEC-19')
       from
         nlas_inventory nlasinv,
         nlas_header hdr
         where
         nlasinv.header_id = hdr.id
         and hdr.location_id = 409
         and hdr.observation_date >= trunc(to_date('31-DEC-19','dd-mon-rr'),'year') 
         and nlasinv.product not in
         (select distinct product from
           nlas_header h,
           nlas_inventory i
           where i.header_id = 1372168) 
order by product, header_id des

c

Upvotes: 1

Views: 59

Answers (2)

Zynon Putney II
Zynon Putney II

Reputation: 695

This can also be accomplished by adding a self join in your where clause

and hdr.header_id = (
   select max(hdr2.header_id) 
   from nlas_header hdr2 
   where hdr2.location_id = hdr.location_id 
   and hdr2.product_id = hdr.product_id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I don't know what your query has to do with the "table" data that you show. But you seem to want row_number():

select t.*
from (select t.*, row_number() over (partition by product order by header_id desc) as seqnum
      from t
     ) t
where seqnum = 1;

If that query is being used to generate the data, then just wrap it in a CTE.

Upvotes: 2

Related Questions