Sometimes, you may need to get first row per group in PostgreSQL. This can be a difficult query using plain SQL. Luckily, PostgreSQL offers window functions for such data analytics. Here’s how to get first row per group in PostgreSQL.
How to Get First Row Per Group in PostgreSQL
Here are the steps to get first row per group in PostgreSQL.
Let’s say you have the following table product_sales that contains productwise sales.
postgres=# create table product_sales(
product varchar(255),
order_date date,
sale int);
postgres=# insert into product_sales(product,order_date, sale)
values('A','2020-05-01',250),
('B','2020-05-01',350),
('C','2020-05-01',1250),
('A','2020-05-02',450),
('B','2020-05-02',650),
('C','2020-05-02',1050),
('A','2020-05-03',150),
('B','2020-05-03',250),
('C','2020-05-03',1850);
postgres=# select * from product_sales;
product | order_date | sale
---------+------------+------
A | 2020-05-01 | 250
B | 2020-05-01 | 350
C | 2020-05-01 | 1250
A | 2020-05-02 | 450
B | 2020-05-02 | 650
C | 2020-05-02 | 1050
A | 2020-05-03 | 150
B | 2020-05-03 | 250
C | 2020-05-03 | 1850
Let’s say you want to get first row in each group, that is, for each product. You can easily get first record for each group using WINDOW FUNCTION.
Bonus Read : How to Get Row Number in PostgreSQL
How to Get First Row Per Group in PostgreSQL
Here’s the SQL query to get first record per group. First we assign row number for each record per group.
postgres=# select
*,
row_number() over (partition by product order by order_date asc)
as row_number
from product_sales;
product | order_date | sale | row_number
---------+------------+------+------------
A | 2020-05-01 | 250 | 1
A | 2020-05-02 | 450 | 2
A | 2020-05-03 | 150 | 3
B | 2020-05-01 | 350 | 1
B | 2020-05-02 | 650 | 2
B | 2020-05-03 | 250 | 3
C | 2020-05-01 | 1250 | 1
C | 2020-05-02 | 1050 | 2
C | 2020-05-03 | 1850 | 3
In the above query, we use row_number() function to assign row number for each record. Since, we need separate numbering of rows for each group, we use PARTITION WINDOW FUNCTION. We tell Postgresql to Partition the rows by each product, that is, and sort the rows for each group by order_date
Next, we just need to use the above result to select rows where row_number=1
postgres=# select *
from (
select
*,
row_number() over (partition by product order by order_date asc)
as row_number
from product_sales
) temp where row_number=1;
product | order_date | sale | row_number
---------+------------+------+------------
A | 2020-05-01 | 250 | 1
B | 2020-05-01 | 350 | 1
C | 2020-05-01 | 1250 | 1
The above query will give you the first record in each group.
Bonus Read : How to Calculate Percentile in PostgreSQL
How to Get Last Row Per Group in PostgreSQL
If you want to get last row per group in PostgreSQL, simply change the sort order in PARTITION clause of above query from ascending to descending.
postgres=# select *
from (
select
*,
row_number() over (partition by product order by order_date desc)
as row_number
from product_sales
) temp where row_number=1;
product | order_date | sale | row_number
---------+------------+------+------------
A | 2020-05-03 | 150 | 1
B | 2020-05-03 | 250 | 1
C | 2020-05-03 | 1850 | 1
Hopefully, you can get first record in each group in PostgreSQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
