sqlite query help

I have 1 table.

jobs = ref( int primary key autoincrement ) 
      _id(int)
      date(date)

_id represents a particular car. each row in the table represents a job for a car including the date the job happened. 1 car can have lots of jobs.

I need 2 sqlite commands

1. command 开发者_Go百科which would get me the most recent job for each and every car in the table.

2. command which would get me all cars which have not had a job for the past 21 days.

Thanks in advance.

Most recent job:

 SELECT _id, max(date) FROM jobs GROUP BY _id

All cars with no job in last 21 days:

 SELECT _id, max(date) FROM jobs GROUP BY _id HAVING max(date) < (date-21)

You'll have to adopt the (date-21) logic depending on how you store dates (SQLite doesn't have a native DATE type).