CODEWITHSUNDEEP
sqldatabasepostgresqltimestampgreatest-n-per-group
James Bennet
James Bennet

Reputation: 603

SQL - Sort by recency where more than a certain value

I have a job table, and a jobstatus table:

Job ( 
    id SERIAL PRIMARY KEY NOT NULL, 
    PackageInstance_id bigint NOT NULL, 
    BuildClient_id bigint 
) 

JobStatus ( 
    id SERIAL PRIMARY KEY NOT NULL, 
    Job_id bigint NOT NULL, 
    Status_id bigint NOT NULL, 
    time timestamp NOT NULL DEFAULT now() 
)

I would like to select all jobs whose most recent reported status is "finished" and that status's timestamp indicates the job finished within the last 10 minutes.

I can already get unfinished jobs, it's the time bit that gets me. I'm assuming we need to do some sort of DATEDIFF, and SELECT where the timestamp is less than x minutes from now() or whatever the function is to get CURRENT_TIMESTAMP.

Upvotes: 1

Views: 458

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658947

SELECT the latest status row per job in a LATERAL subquery, and put your filters in the join clause:

SELECT *
FROM   job j
JOIN   LATERAL (
   SELECT status_id, time
   FROM   jobstatus js
   WHERE  js.job_id = j.id            -- lateral reference
   ORDER  BY js.time DESC NULLS LAST  -- add tiebreaker if tie is possible
   LIMIT  1
    ) js ON js.status_id = 3  -- whatever signifies 'finished'
        AND js.time >= LOCALTIMESTAMP - interval '10 min';

See:

The ORDER BY clause works, because false sorts before true and null. So 'finished' comes first (if it exists). The outer SELECT takes only the finished ones.

Make your status_id type boolean or possibly enum if you have more than 'finished' / 'not finished'. bigint makes no sense.

All your other bigint columns can probably be integer.

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125464

select j.*
from
    job j
    inner join (
        select j.id, max("time") "time"
        from
            job j
            inner join jobstatus js on j.id = js.job_id
        where
            "time" > now() - interval '10 minutes'
            and
            js.status_id = 3 -- finished
        group by j.id
    ) s on s.id = j.id

Upvotes: 2

Piotr Latusek
Piotr Latusek

Reputation: 21

Here is how you can do this:

select
    js.JobId
    , MAX(js.StatusDate) as LatestStatusDate
into #MostRecentStatuses
from #JobStatus js
group by js.JobId

select *
from #MostRecentStatuses mrs
join #JobStatus js
    on js.JobId = mrs.JobId
    and js.StatusDate = mrs.LatestStatusDate
join #JobStatusNames jsn
    on jsn.JobStatusId = js.JobStatusId
join #Jobs j
    on j.Id = mrs.JobId
where
    mrs.LatestStatusDate >= DATEADD(MINUTE, -10, GETDATE())
    and jsn.StatusName = 'Finished'

I assumed your tables are defined like this:

create table #Jobs
(
    Id int
    , Name nvarchar(max)
)

create table #JobStatusNames
(
    JobStatusId int
    , StatusName nvarchar(max)
)

create table #JobStatus
(
    JobId int
    , JobStatusId nvarchar(max)
    , StatusDate datetime
)

Upvotes: 0

sufleR
sufleR

Reputation: 2973

I see that you have status_id so there should be one more table with job_status_names

select * 
from jobs j 
 join ( select 
         job_id, 
         status_id 
       from jobstatus 
       where (job_id, time) in ( select job_id, 
                                        max(time) 
                                 from jobstatus 
                                 where time >= now() - interval '10 minutes'
                                 group by job_id) 
      ) as foo on j.id = foo.job_id 
 where status_id = (select id from job_status_names where status_name = 'finished')

Upvotes: 0

Related Questions