CODEWITHSUNDEEP
sqlsqlitedatetimesql-order-bysql-limit
Oscar Francisco Solis Rodrguez
Oscar Francisco Solis Rodrguez

Reputation: 1

How to select the nearest row date in SQL?

I need help with this SQL query. This is my table:

| id_list   | name      | date_created          | date_updated        |
|-----------|-----------|-----------------------|---------------------|
| 1         | Laundry   | 2020-10-14 12:29:14   | 2020-10-15 08:04:10 |
| 2         | Food      | 2020-10-14 12:38:43   | 2020-10-15 10:45:03 |

How can I select the row with the nearest date_updated? The output has to be like this:

| id_list   | name      | date_created          | date_updated        |
|-----------|-----------|-----------------------|---------------------|
| 2         | Food      | 2020-10-14 12:38:43   | 2020-10-15 10:45:03 |

Upvotes: 0

Views: 47

Answers (2)

GMB
GMB

Reputation: 222542

If you just the one latest row, then order by and limit are sufficient:

select t.*
from mytable t
order by date_updated desc
limit 1

If you want the row that is closest to the current date/time, whether in the past or in the future, then:

select t.*
from mytable t
order by abs(julianday() - julianday(date_updated))
limit 1

Upvotes: 1

Error_2646
Error_2646

Reputation: 3801

If you just want the "nearest" to the current time just use the max. This will return multiple rows in case of a tie.

SELECT *
  FROM A_TABLE
 WHERE date_updated = 
         ( SELECT MAX(date_updated)
             FROM A_TABLE
         )

Upvotes: 2

Related Questions