0

I have a table by the name of purchase_details which has the following data:

purchase_id product_id purchase_price sale_price
3 1 15000 16000
4 1 13000 14000
3 4 500 700
2 4 400 500

I want to select data based on max purchase_id for every product like the following:

purchase_id product_id purchase_price sale_price
4 1 13000 14000
3 4 500 700
MT0
  • 143,790
  • 11
  • 59
  • 117
Arifullah
  • 69
  • 7

1 Answers1

0

One option is to use analytic function (such as row_number or rank) to "sort" data; then extract rows that rank as the highest:

Sample data:

SQL> with purchase_Details (purchase_id, product_id, purchase_price, sale_price) as
  2    (select 3, 1, 15000, 16000 from dual union all
  3     select 4, 1, 13000, 14000 from dual union all
  4     select 3, 4,   500,   700 from dual union all
  5     select 2, 4,   400,   500 from dual
  6    ),

Query begins here:

  7  temp as
  8    (select p.*,
  9       row_number() over (partition by product_id order by purchase_id desc) rn
 10     from purchase_details p
 11    )
 12  select purchase_id, product_id, purchase_price, sale_price
 13  from temp
 14  where rn = 1;

PURCHASE_ID PRODUCT_ID PURCHASE_PRICE SALE_PRICE
----------- ---------- -------------- ----------
          4          1          13000      14000
          3          4            500        700

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Note that ORACLE supports IN() on tuples - not all RDBMS do - so SELECT * FROM purchase_Details WHERE (product_id, purchase_id) IN ( SELECT product_id, MAX(purchase_id) FROM purchase_Details GROUP BY product_id ) ; will also work and may take advantage of a compound index. – p3consulting Oct 16 '22 at 07:38