sqlite query help

Question

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 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.

Answer 1

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).