How to Get Last Row Per Group in PostgreSQL

Sometimes you may need to get last row of table or get last row per group in PostgreSQL. Here’s how to get last record per group in PostgreSQL.

Get Last Row of Table

Let’s say you have the following PostgreSQL table.

postgres=#create table product_sales(product varchar(10),
          order_date date, sale int);

postgres=#insert into product_sales(product, order_date, sale)
values('A','2020-05-01 03:00:00',250),
('B','2020-05-01 05:30:00',350),
('C','2020-05-01 07:45:00',1250),
('A','2020-05-02 02:34:00',450),
('B','2020-05-02 01:24:00',650),
('C','2020-05-02 12:34:00',1050),
('A','2020-05-03 04:00:45',150),
('B','2020-05-03 05:45:00',250),
('C','2020-05-03 09:50:00',1850);

postgres=# select * from product_sales;
 product |      order_date     | sale
---------+---------------------+------
 A       | 2020-05-01 03:00:00 |  250
 B       | 2020-05-01 05:30:00 |  350
 C       | 2020-05-01 07:45:00 | 1250
 A       | 2020-05-02 02:34:00 |  450
 B       | 2020-05-02 01:24:00 |  650
 C       | 2020-05-02 12:34:00 | 1050
 A       | 2020-05-03 04:00:45 |  150
 B       | 2020-05-03 05:45:00 |  250
 C       | 2020-05-03 09:50:00 | 1850

Bonus Read : PostgreSQL Create Function

Here’s the query to get last row of the table, depending on how your rows are sorted. In the above example, since our records are added by timestamp, we select the row with latest time stamp.

postgres=# select * from product_sales 
           order by order_date desc
           limit 1;
 product |     order_date      | sale
---------+---------------------+------
 C       | 2020-05-03 09:50:00 | 1850

In the above query, we sort the rows in descending order of timestamp and select the top 1 row using LIMIT clause.

Bonus Read : PostgreSQL Create Schema

Get Last Record Per Group

Here’s the SQL query to get last row per group, that is, each product.

postgres=# select distinct on (product) product, order_date, sale
           from product_sales
           order by product, order_date desc;

 product |     order_date      | sale
---------+---------------------+------
 A       | 2020-05-03 04:00:45 |  150
 B       | 2020-05-03 05:45:00 |  250
 C       | 2020-05-03 09:50:00 | 1850

In the above query, first we sort the rows by product (ascending) and order_date (descending). Then we use the DISTINCT ON clause to select only the top record per group, which is nothing but the last record per group.

Bonus Read : How to Create Histogram in PostgreSQL

Hopefully, now you can easily get the last row per group in PostgreSQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!