SQL Select New Records Based on Duplicate Records
I have 2 tables, wp_events and wp_merchants.
I have a lot of duplicate rows in the wp_events table, the only difference is the date the column was uploaded (TIMESTAMP) when the record was added to the database. I need a SQL statement that fetches prices from the wp_events table, but if there are duplicates, select the most recent.
This is what I have, but not all entries are selected.
SELECT e1.merchant, e1.price, e1.url, wp_merchants.merchant_logo, wp_merchants.merchant_name, e1.eventname
FROM wp_merchants, wp_events e1
LEFT JOIN wp_events e2 ON ( e1.merchant = e2.merchant
AND e1.uploaddate < e2.uploaddate )
WHERE e2.merchant IS NULL
AND wp_merchants.merchant_name = e1.merchant
ORDER BY price ASC
PLEASE SEE http://sqlfiddle.com/#!2/1db0e/1
source to share
I think this is what you need:
SELECT e.merchant, e.price, e.url, m.merchant_logo, m.merchant_name, e.eventname
FROM wp_events e
INNER JOIN wp_merchants m
ON e.merchant = m.merchant_name
AND e.uploaddate = (SELECT MAX(uploaddate) FROM wp_events WHERE merchant = e.merchant AND eventname = e.eventname)
ORDER BY price ASC
Please check and see what this means.
source to share
You didn't specify the DBMS you are using, perhaps you could use ROW_NUMBER () so you don't need to join the table yourself or use a subquery. This should work in SQL Server, Oracle or PostgreSQL:
SELECT * FROM (
SELECT e.merchant AS merchant_name, e.price, e.url, e.event_name, m.merchant_logo
, ROW_NUMBER() OVER (PARTITION BY e.merchant ORDER BY e.uploaddate DESC) as the_row
FROM wp_events e
INNER JOIN wp_merchants m
ON e.merchant = m.merchant_name
) WHERE the_row = 1
ORDER BY price ASC
If you are using MySQL you will have to use a different method.
source to share