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.
Author |
Topic |
agastyamahi
Starting Member
33 Posts |
Posted - 2014-03-05 : 13:48:33
|
Hi All,I am joining the table below which is table A with another table, Blike table A inner join Table B on A.Emp_Key = B.Emp_KeyWhile 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 NULL3395 110 152 2014-01-01 NULLAfter 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-312271 83 152 2013-01-01 2013-03-31 2899 99 152 2013-04-01 2013-12-313395 110 152 2014-01-01 NULLCan any one please help me how to form a query for this |
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-03-05 : 13:58:08
|
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)_! |
|
|
|
|
|