CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
MAK
MAK

Reputation: 7260

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

Upvotes: 1

Views: 1519

Answers (1)

quantummind
quantummind

Reputation: 2136

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.

Upvotes: 2

Related Questions