1

This question already has an answer here:

I have posted a similar question before, but this one is a little modified.

I have a table with 4 columns as shown below.

ITEMCHARGE     CPT4       EFF_DATE    CPT4MOD
326.8         29075       20101204    GO
326.8         29075       20110104    GO
326.8         29075       20110204    GO
326.8         29075       20110406    GO
352.69        29075       20090611    GO
352.69        29075       20090917    GO
352.69        29075       20100614    GO
352.69        29075       20100722    GO

Now, if CPT4 and CPT4MOD match, I need to keep the rows with distinct ITEMCHARGE (with latest EFF_DATE). The output should be like below:

    ITEMCHARGE  CPT4    EFF_DATE    CPT4MOD
       326.8    29075   20110406    GO
       352.69   29075   20100722    GO

There are 2 distinct ITEMCHARGE for the same CPT4 and CPT4MOD. So we took the latest EFF_DATE row for both of them. It would be very helpful If I can get a linq (with lamda) and SQL version for this.

marked as duplicate by ypercubeᵀᴹ, RLF, RolandoMySQLDBA, Mark Storey-Smith sql-server Dec 22 '14 at 17:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • it's 4 columns, not 4 rows :) – RayofCommand Dec 22 '14 at 13:08
0

Would that work? SQL version only.

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CPT4, CPT4MOD, ITEMCHARGE ORDER BY EFF_DATE DESC) AS RowNo
    , *
FROM #table
) AS T
WHERE T.RowNo = 1

You can check it here: http://data.stackexchange.com/stackoverflow/query/260027/delting-duplicate-rows-with-condition

  • How do I convet this to LINQ? – Rajesh Dec 22 '14 at 13:52
  • I'm just a SQL Developer. :( Would this tool help? link – Evaldas Buinauskas Dec 22 '14 at 14:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.