How To Nearest Date In The Future To Between Today With Sql (mysql)
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?
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
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
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key