开窗函数---返回分组后的一条数据

SQL:

1:分组后返回的数据

select e.vendor_id,
       e.item_id,
       e.unit_price,
       row_number() over(partition by e.vendor_id, e.item_id order by e.unit_price asc) row_num
  from cux_po_view_v e
 where e.vendor_id = 1120
   and e.item_id in (2117, 2032);

结果

 

1：分组后取返回结果的最后一条数据

select ee.vendor_id, ee.item_id, ee.unit_price, ee.row_num
  from (select e.vendor_id,
               e.item_id,
               e.unit_price,
               row_number() over(partition by e.vendor_id, e.item_id order by e.unit_price asc) row_num
          from cux_po_view_v e
         where e.vendor_id = 1120 and e.item_id in(2117,2032)) ee
 where ee.row_num = 1;