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.

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...