Returning Most Recent Record For Each Job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning Most Recent Record For Each Job

Author

Topic

janeyjamjar
Starting Member

2 Posts

Posted - 2009-12-07 : 13:15:55

Hi All,

I'm really new to this so not sure how to go about getting the result I need. I have the following query string, which returns a recordset, but I need the single most recent record for each "Job", whereas this returns me all the distinct lines for the Job.

"SELECT DISTINCT Job, Priority, Submit, Design, MAX(JobDate) FROM [Sheet1$] WHERE Job LIKE '%" & strProject & "-%' GROUP BY Job, Priority, Submit, Design"

Any ideas/suggestions welcome.

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 13:20:02

If you are using SQL server 2005 or above..you can try this..

SELECT * FROM 
(
SELECT row_number() over(partition by Job order by JobDate Desc) as seq, 
Job, 
Priority, 
Submit, 
Design, 
JobDate 
FROM [Sheet1$] 
WHERE Job LIKE '%" & strProject & "-%' 
) T
WHERE T.seq = 1

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-12-07 : 13:20:32

Top 1 * & order by will do it for you.

regards,
Anil.

select distinct top 1 Job, Priority, Submit, Design, MAX(JobDate) FROM [Sheet1$] WHERE Job LIKE '%" & strProject & "-%' GROUP BY Job, Priority, Submit, Design
order by max(jobdate)
---add the criteria that defines the most recent record & I guess it is the max(jobdate)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 01:38:18

quote:
Originally posted by Kumar_Anil

Top 1 * & order by will do it for you.

regards,
Anil.

select distinct top 1 Job, Priority, Submit, Design, MAX(JobDate) FROM [Sheet1$] WHERE Job LIKE '%" & strProject & "-%' GROUP BY Job, Priority, Submit, Design
order by max(jobdate)
---add the criteria that defines the most recent record & I guess it is the max(jobdate)

OP need top 1 record for each job id
See the reply posted by vijayisonly

Madhivanan

Failing to plan is Planning to fail

janeyjamjar
Starting Member

2 Posts

Posted - 2009-12-10 : 13:42:10

Thanks all, managed to get it working with your help!!

Janey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources