How to check for only last value related to specific id

I am trying to write query which would insert data from one table to another table but only in case if last email history record holds different email value than it is in customers table.

I'm talking here about next scenario:

There is one table called customers and it looks like this:

Customer_Id
Customer_Email

There is second table called customers_email_history and it looks like this:

Customer_Email_History_Id
Customer_Id
Customer_Email

I would like to insert data from customers table which holds current customer_email value to table customer_email_history but only in case if customer_email from customers table is different than last record (newest record) related to that customer in customers_email_history. Here is an example:

SCENARIO 1: DO NOT INSERT DATA

As it is possible to see in table customers_email_history last row related to customer with id 1 is his current email from table customers so we wont insert new row in customers_email_history.

SCENARIO 2: INSERT DATA

As it is possbile to see for customer with id 2 we should insert new row to customers_email_history table since last (newest) row added related to that customer is not same as his current email from customers table. Customer table holds [email protected] email while email history table holds [email protected] so we should insert [email protected] to customers_email_history table

I tried to write something like this, but it is not working :(

SELECT T1.id, T1.email
FROM customers AS T1 INNER JOIN customers_email_history AS T2
on T1.id = T2.customer_id
WHERE T1.email != (SELECT T2.email FROM email_history ORDER BY ID DESC LIMIT 1) -- here i tried to get last (newest email) email related to that customer and to compare it 
with current email but this aint work liks this :(

SQL Fiddle

CodePudding user response：

Try this

SELECT T1.id, T1.email
FROM Customers AS T1
WHERE T1.email != (SELECT T2.email FROM Customers_Email_History AS T2 WHERE T1.Id = T2.Customer_ID ORDER BY ID DESC LIMIT 1)

CodePudding user response：

Looks like a perfect case for EXCEPT ALL:

INSERT INTO customers_email_history(customer_id, email)
SELECT id, email
FROM   customers
EXCEPT ALL
(
SELECT DISTINCT ON (customer_id)
       customer_id, email
FROM   customers_email_history
ORDER  BY customer_id, id DESC
);

db<>fiddle here

See:

Select rows which are not present in other table

Assuming that customers_email_history.id really is an autoincrement column like serial or an IDENTITY column. See:

Auto increment table column

Else you need add a manual ID.

Depending on undisclosed Postgres version, cardinalities, table definition and data distribution, there may be (much) faster solutions. See: