SQL query for entries of last N jobs

I have the following table:

UnixTime  JobID  Status   Data
 1010        1   start     ...
 1012        1   running   ...
 1013        2   start     ...
 1015        1   ok        ...
 1016        2   running   ...
 1017        3   begin     ...
 1018        2   error     ...
 1021        3   running   ...
 1022        4   start     ...
 1023        5   start     ...

etc, so basically jobs keep adding entries concurrently

I am looking for an efficient query to return all entries of the latest (by time) N jobs

So find the first (by time) entry for each job, then using that get the latest N jobs with all their entries

So for the above table and N=3 I will get:

 1017        3   begin     ...
 1021        3   running   ...
 1022        4   start     ...
 1023        5   start     ...

I understand such query will involve PARTITION BY but not sure how to proceed ?

(I need a valid SqlLite query)

Answer

You need this query:

SELECT JobID
FROM tablename
GROUP BY JobID
ORDER BY MAX(UnixTime) DESC LIMIT 3

that returns the last 3 distinct JobIDs.

You can use it with the operator IN:

SELECT *
FROM tablename
WHERE JobID IN (
  SELECT JobID
  FROM tablename
  GROUP BY JobID
  ORDER BY MAX(UnixTime) DESC LIMIT 3
)
ORDER BY UnixTime;

See the demo.