Postgres Get First And Last Version For Individual Vendor

I have mapping table for RFQ(request for quotation) and Vendor's bid amount with version. Table : id rfq_id(FK) vendor_id(FK) amount version ----------------------------------

Solution 1:

Window functions add columns to all the existing rows, instead of grouping input rows into a single output row. Since you are only interested in the bid values, use a DISTINCT clause on the fields of interest.

Note that you need a frame clause for the WINDOW definition to make sure that all rows in the partition are considered. By default, the frame in the partition (the rows that are being used in calculations) runs from the beginning of the partition to the current row. Therefore, the last_value() window function always returns the value of the current row; use a frame of UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING to extend the frame to the entire partition.

SELECTDISTINCT
  vendor_id,
  version,
  amount,
  first_value(amount) OVER w AS first_bid,
  last_value(amount) OVER w AS last_bid
  row_number() over w as rn
FROM   
   rfq_vendor_version_mapping
WHERE rfq_id =1WINDOW w AS (PARTITIONBY vendor_id ORDERBY version
             ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDERBY vendor_id;

Solution 2:

You have to GROUP BY vendor_id because you want just one row per vendor_id:

SELECT 
  vendor_id,
  MAX(CASEWHEN rn = 1THEN amount END) AS first_bid,
  MAX(CASEWHEN rn2 = 1THEN amount END) AS last_bid
FROM (  
   SELECT 
     vendor_id,
     version,
     amount,
     row_number() over (PARTITION BY vendor_id orderBY version) as rn,
     row_number() over (PARTITION BY vendor_id orderBY version DESC) as rn2
   FROM   
      rfq_vendor_version_mapping
   WHERE
      rfq_id=1) AS t
GROUPBY vendor_id 
ORDERby vendor_id;

The query uses conditional aggregation in order to extract amount values that correspond to first and last bid.

Demo here

Solution 3:

Without ORDER BY OLAP-functions default to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING but with ORDER BY this changes to ROW UNBOUNDED PRECEDING.

You were quite close, but you need two different windows:

select vendor_id, amount as first_bid, last_bid
from 
 (
    SELECT 
      vendor_id,
      version,
      amount,
      last_value(amount) -- highest version's bidover (PARTITIONBY vendor_id 
            orderby version
            rowsbetween unbiunded preceding and unbounded following) as last_bid,
      row_number()
      over (PARTITIONBY vendor_id 
            orderby version) as rn
    FROM   
       rfq_vendor_version_mapping
    where
       rfq_id=1 
 ) as dt
where rn =1-- row with first version/bidORDERby vendor_id;