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/
Go to Top of Page
   

- Advertisement -