How to nearest date in the future to between today with sql (mysql)-CodePudding

I want to see the report date with the nearest future date. I want it to be a single record based on implementation id. Today 02-18-2022

SELECT   *
   FROM     document_reports
   WHERE    reportDate >= now() AND reportDate in 
   (SELECT MIN(reportDate) 
   FROM document_reports 
   WHERE reportDate >= NOW()
   group by implementation_id )
   
   group by implementation_id
   order by reportDate

You can check the tables in the below

Please help me? Where did I make mistake?

CodePudding user response：

If it's MySQL 5.x, then I'd use a correlated sub-query, or a join on an aggregate sub-query...

SELECT
  *
FROM
  document_reports
WHERE
  reportDate = (
    SELECT MIN(reportDate)
      FROM document_reports   AS lookup
     WHERE lookup.implementation_id  = document_reports.implementation_id
       AND lookup.reportDate        >= NOW()
  )
ORDER BY
  reportDate

Or...

SELECT
  document_reports.*
FROM
(
  SELECT implementation_id, MIN(reportDate) AS minReportDate
    FROM document_reports
   WHERE reportDate >= NOW()
GROUP BY implementation_id
)
  AS lookup
INNER JOIN
  document_reports
    ON  document_reports.implementation_id = lookup.implmentation_id
    AND document_reports.reportDate        = lookup.minReportDate
ORDER BY
  document_reports.reportDate

If it's MySQL 8 then I'd use ROW_NUMBER()

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY implementation_id
                           ORDER BY reportDate
                      )
                        AS rn
  FROM
    document_reports
  WHERE
    reportDate >= NOW()
)
SELECT
  *
FROM
  sorted
WHERE
  rn = 1