CODEWITHSUNDEEP
phpmysqlsql
Priyanka Khade
Priyanka Khade

Reputation: 450

Min Date find from database

I have a query as per below:

SELECT
    IsTestBatch,
    BatchID,
    CampaignID,
    IF(ActualTriggerDateTime IS NOT NULL,
       MIN(ActualTriggerDateTime),
       MIN(TriggerDateTime)) AS NextQueuedOn
FROM
    campaignbatches
WHERE
    Status IN (2,3)
GROUP BY
    CampaignID
ORDER BY
    IF(ActualTriggerDateTime IS NOT NULL, ActualTriggerDateTime, TriggerDateTime) ASC

The if condition is not working properly, and the MIN date is not correct as I expect.

enter image description here

I expect the above record, but it returns the first record from the table for the previous image:

Upvotes: 3

Views: 611

Answers (2)

Shidersz
Shidersz

Reputation: 17190

Since the aggregation function MIN() ignore NULL values, you can try to select the minimum date from column ActualTriggerDateTime, and in the special case that all the values are NULL on this column, then select the minimum value from the column TriggerDateTime.

SELECT
    IsTestBatch,
    BatchID,
    CampaignID,
    IFNULL(MIN(ActualTriggerDateTime), MIN(TriggerDateTime)) AS NextQueuedOn
FROM
    campaignbatches
WHERE
    Status IN (2,3)
GROUP BY
    IsTestBatch, BatchID, CampaignID
ORDER BY
    NextQueuedOn ASC

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

All the selection column will come to group by clause

SELECT IsTestBatch,BatchID,CampaignID,
 coalesce(MIN(ActualTriggerDateTime),MIN(TriggerDateTime)) AS NextQueuedOn 
 FROM campaignbatches
WHERE Status IN (2,3)
   and IsTestBatch=1 and BatchID=42 -- as you want single row
 GROUP BY IsTestBatch,BatchID,
   CampaignID 
ORDER BY NextQueuedOn ASC

Upvotes: 0

Related Questions