PostgreSQL: How to select last record of each user

Before writing a PostgreSQL query, we will create a scenario, suppose we have two tables first are Customer, and Second is its Orders. First, we will create tables through the following query then we will write the query to select the records that most recently ordered by a customer.

--Create Customer table CREATE TABLE customer (        cust_id int generated always as identity,        name text,        phone text ); --Create Order table CREATE TABLE orders (        id int generated always as identity,        cust_id int,        qty int,        price decimal,        created_date date ); --Let us insert some records in the customer table INSERT INTO customer (name, phone) VALUES('Dilip','9091228998'),          ('Anil','9099887886'),          ('Ashish','9801228990');          --Let us insert some records in the order table INSERT INTO orders ( cust_id, qty, price,created_date ) VALUES(1, 2, 10.00, '21/04/2020'),          (2, 4, 30.00, '22/04/2020'),          (3, 5, 60.00, '21/04/2020'),          (1, 6, 10.00, '22/04/2020'),          (2, 7, 30.00, '23/04/2020'),          (3, 5, 90.00, '25/04/2020'),          (2, 6, 86.00, '24/04/2020');

           

Using Lateral join we can find the expected result set.

Using LATERAL JOIN in PostgreSQL

SELECT c.cust_id,c.name, o.created_date, o.qty, o.price FROM   customer c CROSS  JOIN LATERAL (    SELECT o.created_date, o.qty, o.price    FROM   orders o    WHERE  o.cust_id = c.cust_id            AND    o.created_date <= current_date    ORDER  BY o.created_date DESC NULLS LAST    LIMIT  1    ) o;

Result:

You can replace current_date with a parameter to get the records according to your desire date.