Problem selecting correct duplicate row

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

Transact-SQL (2008)

Problem selecting correct duplicate row

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2014-12-12 : 14:05:18

I have the following data in my SQL table, tMostCurrData :

ODate Company LastChgDate EmpNum Status

11/1/2014 X123 11/15/2014 707123 A
11/1/2014 X123 11/23/2014 707123 T

There are actually 90 or so columns but I simplified it for this post. In the above case, I want to select the row with the most recent LastChgDate. So the second one since it is more recent.

Here is my SQL statement. How do I change it so that I always select the row withe most recent LastChgDt?

@FromDate datetime

SELECT DISTINCT
tk.Company
,tk.EmpNum
,tk.Dept]
,tc.CompanyName
,td.DepartmentName
,hc.GLCode
,hc.HRContact
FROM tMostCurrData tk LEFT OUTER JOIN
tDepartmentTbl td
ON tk.dept = td.Department LEFT OUTER JOIN
tCompanyTbl tc
ON tk.Company = tc.Company LEFT OUTER JOIN
HRContacts hc
ON tk.dept = hc.Dept
WHERE tk.FtPt IN ('FT', 'IO') AND
Case When NOT tk.RDt = '1900-01-01'
Then tk.RDt
Else
tk.HDt
End BETWEEN DATEADD(d, -90, @FromDate) AND
DATEADD(d, -60, @FromDate)
ORDER BY tk.Company, tk.EmpNum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-12 : 14:10:32

Use a CTE that groups by the duplicate columns and gets the MAX. Join to the CTE in your query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources