Compute the difference between two dates in SQL and select the maximum difference

Question

I want to compute the difference between two dates and then select the maximum difference using SQL. If there is more than one maximum difference, I want to select one of them. Given the random nature of the max() function, how can I ensure the SQL will return the same row every time?

I am using PostgreSQL as the database.

Answer 1

If there are multiple rows with the max difference, SQL does not guarantee which row will be returned. If you need a deterministic result, you can add more criteria to your ORDER BY clause. 

Here is an SQL for the PostgreSQL:

SELECT 

    some_column, 

    (date1 - date2) AS date_diff  

FROM 

    your_table

ORDER BY 

    date_diff DESC, 

    some_column ASC

LIMIT 1;

By adding "some_column ASC", it will make sure that SQL returns the same row every time you run the SQL.