select most recent - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

select most recent

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-03-26 : 11:15:02

Here is my SELECT statement...

SELECT emp_id, job_cde, begin_dte, end_dte
FROM job_master

I would like to return the most recent begin_dte based on emp_id and job_cde. I don't care about end_dte. I just want to return whatever end_dte happens to be in the same row as the most recent begin_dte in the job_master table.

I tried using MAX, but I don't want to include end_dte in the GROUP BY clause since it will return multiple rows for each distinct emp_id/job_cde combination.

Any thoughts? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 11:18:13

[code]SELECT emp_id,
job_cde,
begin_dte,
end_dte
from (
SELECT emp_id,
job_cde,
begin_dte,
end_dte,
row_number() Over (partition by emp_id, job_cde order by begin_dte desc) as seqid
FROM job_master
) AS d
where seqid = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 11:19:53

You haven't given much information...but..Maybe this...?

SELECT D.emp_id, D.job_cde, D.begin_dte, D.end_dte
(SELECT emp_id, job_cde, begin_dte, end_dte,row_number () over(partition by emp_id,job_cde order by begin_dte desc) as seq
FROM job_master) D
WHERE D.seq = 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 11:20:53

Sorry Peso..I must have been typing my response when had already posted...Need to improve my typing skills

eevans
Starting Member

48 Posts

Posted - 2009-03-26 : 11:24:24

That works. Thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources