SQL query for entries of last N jobs-CodePudding

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)

CodePudding user response：

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.

CodePudding user response：

Hmmm . . . If the jobs are incrementing sequentially, you can use dense_rank():

select t.*
from (select t.*,
             dense_rank() over (order by jobid desc) as seqnum
      from t
     ) t
where seqnum <= 3  -- or whatever number you want
order by unixtime;

If you need to go by the times, you can first get the earliest time for each job and then use dense_rank():

select t.*
from (select t.*,
             dense_rank() over (order by min_unixtime desc, jobid desc) as seqnum
      from (select t.*,
                   min(unixtime) over (partition by jobid) as min_unixtime
            from t
           ) t
     ) t
where seqnum <= 3  -- or whatever number you want
order by unixtime;

Another fun method uses aggregation:

select t.*
from t join
     (select jobid, min(unixtime) as min_unixtime
      from t
      group by jobid
      order by min_unixtime desc
      limit 3
     ) t3
     on t.jobid = t3.jobid