Want to know the latest record

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 2000 Forums

SQL Server Development (2000)

Want to know the latest record

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-04-17 : 22:15:16

Hi,

My table as follow,

tblEmployer
TrnxID| EmployerID| setRR| CrtDte | UpdDte
------------------------------------------------------
1 | S0852 | jui | 4/12/2009 |
2 | P0004 | jui | 6/12/2008 | 7/12/2008
3 | N0003 | mik | 11/23/2008 | 8/3/2009
4 | S0852 | jui | 10/12/2008 | 2/12/2009
5 | P0004 | jui | 10/12/2008 | 2/12/2009

Hint
1. CrtDte will set, after perform insert statement - only 1
2. UpdDte will set, after perform update statement

Based on EmployerID, how did i list all latest record? As a result my expected result as
1 | S0852 | jui | 4/12/2009 |
3 | N0003 | mik | 11/23/2008 | 8/3/2009
5 | P0004 | jui | 10/12/2008 | 2/12/2009

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-17 : 23:55:00

Your output should be :

1 | S0852 | jui | 4/12/2009 | 
3 | N0003 | mik | 11/23/2008 | 8/3/2009
4 | S0852 | jui | 10/12/2008 | 2/12/2009
5 | P0004 | jui | 10/12/2008 | 2/12/2009

First Verify this is what you are looking for .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:25:37

[code]SELECT t.*
FROM YourTable t
INNER JOIN (SELECT EmployerID,MAX(COALESCE(UpdDte,CrtDte)) AS Latest
FROM YourTable
GROUP BY EMployerID)t1
ON t1.EmployerID=t.EmployerID
AND t1.Latest=COALESCE(t.UpdDte,t.CrtDte)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources