Need help in SQL query to get recently created job record with status

Question

I need distinct Jobid with recent status

jobs table
------------------ 
JobId Status CreatedDate 
------------------
1     sent   4/24/2018  
1     draft  4/23/2018 
2     sent   4/22/2018
2     draft  4/21/2018
1     req    4/20/2018

I need result like this

---------------
JobId  Status
---------------
1       Sent
2       Sent

I tried this but giving duplicate jobids with different statuses records

select distinct JobId, Status, CreatedDate from Jobs order by CreatedDate desc

I tried this :select distinct JobId, Status, CreatedDate from Jobs order by CreatedDate desc — Diya, Apr 24 '18 at 17:02
@Diya If you don't need `CretedDate` on the result, why would you include it in your query??? Remove it. — Eric, Apr 24 '18 at 17:02
Possible duplicate of [how do I query sql for a latest record date for each user](https://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user) — GSazheniuk, Apr 24 '18 at 17:06

score 0 · Accepted Answer · answered Apr 24 '18 at 17:03

0

I would suggest:

select j.*
from jobs j
where j.CreatedDate = (select max(j2.CreatedDate) from jobs j2 where j2.jobid = j.jobid);

If you have an index on jobs(jobid, CreatedDate), then this is probably the fastest option.

answered Apr 24 '18 at 17:03

Gordon Linoff

1,242,037
58
646
786

This could show duplicates if multiple results for a single job on same day. – rdbradshaw Apr 24 '18 at 17:05

score 0 · Answer 2 · answered Apr 24 '18 at 17:05

0

Try This:

SELECT 
    JobId,
    Status
FROM
    jobs j,
    (
        SELECT JobId, max(CreatedDate) cd
        FROM jobs
        GROUP BY JobId
    ) as t
where j.jobid = t.jobid
and cd = j.CreatedDate

answered Apr 24 '18 at 17:05

Rahul Jain

1,319
7
16

score 0 · Answer 3 · answered Apr 24 '18 at 17:09

Considering you are using SQL Server version that supports Common-Table-Expressions, I would use following query:

;with cte as (
    select 
        *, 
        ROW_NUMBER() over (PARTITION BY JobId ORDER BY CreatedDate desc) AS RN 
    from jobs)

select * from cte where RN = 1

Need help in SQL query to get recently created job record with status

3 Answers3