CODEWITHSUNDEEP
mysql
user1157690
user1157690

Reputation: 373

Get the closest value from mysql table

This might be a simple one, but since I don't have much knowledge about MySQL I don't know how to do this, This is what I basically want,

I have a query like this

//time format "yyyy-MM-dd"

SELECT ID 
FROM `id_table` 
WHERE time > "2012-01-05 " AND time < "2012-01-10";

But in the id_table I have data only up to 2012-01-04 then it starts again from "2012-01-20", so above query would return null. Is there a any way where I can retrieve the last data record from the table, as for this example can I get the ID of 2012-01-04 date from the table when I query like this

SELECT ID 
FROM `id_table` 
WHERE time > "2012-01-05"

Upvotes: 0

Views: 1080

Answers (3)

Ben English
Ben English

Reputation: 3918

To get the record closest to the given time ( you could easily tweak this if you care about dealing with duplicates, if that doesn't matter then this alone should suffice)

 SELECT
    ID
 FROM
    id_table
 ORDER BY ABS(DATEDIFF(time, '2012-01-05'))
 LIMIT 1

NOTE: If the time field is a time value yyyy-mm-dd hh:mm:ss then you can use TIMEDIFF for a more accurate comparison.

OP If you will explain more on how you would like to handle different cases I can tweak this example to suit them. Cheers.

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

SELECT ID FROM id_table
WHERE time between
  least((select max(time) from id_table), "2012-01-05") AND "2012-01-10";

Note that between will get data from "2012-01-10" and ("2012-01-05" OR "2012-01-05)

Upvotes: 0

cctan
cctan

Reputation: 2023

Are you looking for the one (i assume max ID) ID of the row with the nearest time to 2010-01-05?

SELECT MAX(ID) as LastId FROM id_table 
WHERE time = (SELECT MAX(time) 
              FROM id_table WHERE time < '2012-01-05')

Upvotes: 3

Related Questions