Get last two ids from table based on condition

Question

I was wanting to know how I get the last two ids from the table based on certain conditions

for example, if today is 1st July, I want to get the id of record for today and then the id of the record before this.

Please note that the id for the previous record might be different, as this is sorted by date, so previous_id = current_id - 1 would not work.

Thanks for your time

Sunil

Answer 1

Assuming your dates are stored in the database as Unix timestamps:

$date='07/01/2010';
$date1=strtotime($date);
$date1=strtotime($date.'-1 day');

$date1_id=mysql_query('SELECT id FROM dates WHERE date='.$date1);
$date2_id=mysql_query('SELECT id FROM dates WHERE date='.$date2);

Answer 2

my mysql db is down, and I've been in oracle & java all day, but give this a shot.

$date='07/01/2010'; $date1=strtotime($date);

$date1_id=mysql_query('SELECT max(id) FROM dates WHERE date='.$date1); $date2_id=mysql_query('SELECT max(id) FROM (select id from dates where date='.$date1.' and id !='.$date1_id.')');

i hope two queries are ok,and i think mysql supports subqueries in the from clause, can't remember.

Answer 3

SELECT id, date FROM dates ORDER BY date DESC LIMIT 2;

maybe add AND date <= now() if you have dates after today that you'd like to ignore.