SQL Server : if rows have same column values then take the row with max of another column

Question

Table:

ID   A       B            C          D    E
----------------------------------------------
1    4   370692690  917400014333    307   990
2    4   370692690  917400014333    392   767
3    2   370692690  917400014333    337   367
4    3   370692690  917400014333    269   284

If rows have matching A,B and C columns (in this case row 1 & 2) then select row with MAX(D).

Result:

ID   A       B            C          D    E
----------------------------------------------
2    4   370692690  917400014333    392   767
3    2   370692690  917400014333    337   367
4    3   370692690  917400014333    269   284

It's been asked several times. https://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate/7118233 — Eric Yang, Apr 17 '18 at 17:27
SELECT * FROM tmpTable WHERE ID IN (SELECT MAX(ID) FROM tmpTable GROUP BY A, B, C); — DxTx, Apr 17 '18 at 17:55
Answered and re-closed as duplicate (although the duplicate only looks for 1 matching column, not 3) — Andomar, Apr 17 '18 at 18:22

score 0 · Answer 1 · answered Apr 17 '18 at 18:21

Here's an example with 3 columns and highest ID:

select  *
from    (
        select  row_number() over (partition by A, B, C order by ID desc) as rn
        ,       *
        from    Table1
        ) sub
where   rn = 1  -- Only highest ID per A, B, C group

Example at SQL Fiddle.

SQL Server : if rows have same column values then take the row with max of another column

1 Answers1