Select rows with max of duplicates along with distinct data in mysql

I have a scenario in which I need to get the last 3 hours data from a table, by joining it with another table. Please find the 2 tables below

1.PURCHASE TABLE

| PURCHASE_ID | ORDER_NO | PRODUCT_TYPE | PURCHASE_DATE       | CREATE_DATE         | MODIFIED_DATE       |
|-------------|----------|--------------|---------------------|---------------------|---------------------|
|    500      | 612356   |  FOOD        | 2020-02-26 08:26:34 | 2020-02-26 08:26:34 | 2020-02-26 08:26:34 |
|    501      | 975432   |  FOOD        | 2020-02-26 08:29:12 | 2020-02-26 08:29:12 | 2020-02-26 08:29:12 |
|    502      | 156712   |  ELECTRONICS | 2020-02-26 08:32:18 | 2020-02-26 08:32:18 | 2020-02-26 08:32:18 |

1.PURCHASE_DETAIL TABLE

| PURCHASE_DETAIL_ID | PURCHASE_ID | MESSAGE_STATUS | CREATE_DATE         | MODIFIED_DATE      |   
|--------------------|-------------|----------------|---------------------|--------------------|
|    1011            |      500    |      FAIL      | 2020-02-26 08:27:14 | 2020-02-26 08:27:14|
|    1012            |      500    |      SUCCESS   | 2020-02-26 08:27:56 | 2020-02-26 08:27:56|
|    1013            |      501    |      FAIL      | 2020-02-26 08:29:14 | 2020-02-26 08:29:14|
|    1014            |      501    |      SUCCESS   | 2020-02-26 08:29:58 | 2020-02-26 08:29:58|
|    1015            |      501    |      SUCCESS   | 2020-02-26 08:30:02 | 2020-02-26 08:30:02|
|    1016            |      501    |      FAIL      | 2020-02-26 08:30:37 | 2020-02-26 08:30:37|
|    1017            |      501    |      SUCCESS   | 2020-02-26 08:31:22 | 2020-02-26 08:31:22|
|    1018            |      502    |      FAIL      | 2020-02-26 08:32:24 | 2020-02-26 08:32:24|
|    1019            |      502    |      SUCCESS   | 2020-02-26 08:32:49 | 2020-02-26 08:32:49|
|    1020            |      502    |      FAIL      | 2020-02-26 08:33:03 | 2020-02-26 08:33:03|

The result should be only the one with the latest MESSAGE_STATUS as failure and the table is huge, I just need the last 3 hours data only.

As the result I would like to have the following data (I need the ORDER_NO only)

1.PURCHASE TABLE

| PURCHASE_ID | ORDER_NO | PRODUCT_TYPE |
|-------------|----------|--------------|
|    502      | 156712   |  ELECTRONICS |

The best query I came up with is

SELECT PURCHASE_ID, ORDER_NO, PRODUCT_TYPE FROM PURCHASE WHERE PURCHASE_ID IN (
SELECT A.PURCHASE_ID FROM PURCHASE A INNER JOIN
(SELECT PURCHASE_ID, MAX(PURCHASE_DETAIL_ID) AS NEW_ID, MESSAGE_STATUS 
FROM PURCHASE_DETAIL WHERE CREATE_DATE > DATE_SUB(NOW(),INTERVAL 3 HOUR) GROUP BY PURCHASE_ID) B
ON A.PURCHASE_ID = B.PURCHASE_ID AND A.PURCHASE_DETAIL_ID = B.NEW_ID 
AND A.MESSAGE_STATUS = "FAIL"
)

But this is not giving me the expected results. Can anyone please have a look and let me know what I am missing. I am still a novice at SQL.

Answer

You can solve this by doing inner join and order by clauses

SELECT PURCHASE_ID, ORDER_NO, PRODUCT_TYPE
FROM purchase p INNER JOIN purchase_detail pd  ON p.purchase_id = pd.purchase_id WHERE 
message_status='fail' AND pd.create_date BETWEEN date.now() AND date.now()-3 ORDER BY 
pd.create_date DESC LIMIT 1

This will fetch all records within three hours window and fetch the latest record which has the MESSAGE_STATUS as fail

Advertisement

Answer