I am trying to get the row with maximal negative date difference (
date - Now() for example ) from a table.
Something like this ( pseudo code ):
SELECT ID, MAX( DateDiff("d", Now(), myDate) ) AS [test] FROM myTable WHERE test < 0;
The result would be something like this:
ID | test 1 | -15
I have tried to solve this on my own but have failed. I do not even know how to phrase the search question on Google, so Internet can not help me. I am just stuck.
Maximal negative number example: between -10 and -125, result should be -10.
Trying out on my own, I was able to come close:
SELECT MAX( DateDiff("d", myDate, Now()) as [test] from myTable WHERE DateDiff("d", myDate, Now() < 0;
It returns correct date difference, but when I try to add ID as well, it returns date difference for all the rows. I can not get single value.