SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help with query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

agastyamahi
Starting Member

Canada
32 Posts

Posted - 03/05/2014 :  13:48:33  Show Profile  Reply with Quote

Hi All,

I am joining the table below which is table A with another table, B
like table A inner join Table B on A.Emp_Key = B.Emp_Key
While I am joining there are duplicates in the last two records, I want to pick the record with the max ed_key. I want to write a subquery near the join to pull the latest ed_key when there are duplicates.


Ed_Key D_key Emp_Key FromDt ToDt
1772 35 152 2011-06-14 2012-12-31
2271 83 152 2013-01-01 2013-03-31
2899 99 152 2013-04-01 2013-12-31
3256 110 152 2014-01-01 NULL
3395 110 152 2014-01-01 NULL


After creating the sub query my table and joining with table B, my table A should have the records like this below

Ed_Key D_key Emp_Key FromDt ToDt
1772 35 152 2011-06-14 2012-12-31
2271 83 152 2013-01-01 2013-03-31
2899 99 152 2013-04-01 2013-12-31
3395 110 152 2014-01-01 NULL

Can any one please help me how to form a query for this

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/05/2014 :  13:58:08  Show Profile  Reply with Quote
1) It is not a duplicate record because the Ed_key is different for all of them.
2) If you want to pull the max(ed_key), then first find all the rows with MAX(ed_key) OVER (PARTITION BY emp_key ORDER BY emp_key) and then do join.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000