MAK MAK - 8 months ago 44
SQL Question

PostgreSQL : Get first and last inserted record for each ID column

I have the following table with two columns.

Table:

create table tbl1
(
p_id int,
p_price int
);


Insertion:

INSERT INTO tbl1 values(1,100);
INSERT INTO tbl1 values(1,50);
INSERT INTO tbl1 values(1,20);

INSERT INTO tbl1 values(2,10);
INSERT INTO tbl1 values(2,20);

INSERT INTO tbl1 values(3,22);
INSERT INTO tbl1 values(3,89);
INSERT INTO tbl1 values(3,500);


Query: Following query gives me the row number of each row.

SELECT p_id,p_price,row_number() over(partition by p_id order by p_id) rn
from tbl1


I want to get only first and last inserted record for each product id (p_id).

Expected Result:

p_id p_price
-----------------
1 100
1 20
2 10
2 20
3 22
3 500

Answer

You can do this using subquery:

SELECT p_id, p_price, rn from (
  SELECT *, last_value(rn) over(partition by p_id) as last  from (
    SELECT p_id,p_price,row_number() over(partition by p_id order by p_id) rn
    FROM tbl1
  ) s1
) s2 where rn=1 or rn=last;

So at inner select you get the row number by partition, one level up you get the last row numbers (first is always 1). Then the top level can do the filter.