SQL Group by Max not desired results-CodePudding

I'm using SQL Server 2014 and have a table like this:

PK    ContactID  Tele    Mob   Land  Email  Txt
1       10        1       0     1     1      1
2       10        0       1     0     0      1
3       12        1       3     1     1      1

The desired result is:

PK    ContactID  Tele    Mob   Land  Email  Txt
2       10        0       1     0     0      1
3       12        1       3     1     1      1

However, if I perform a GroupBy \ Max:

SELECT        MAX(PK) AS PK, ContactID, Tele, Mob, Land, Email, Txt
FROM            Contacts
GROUP BY ContactID, Tele, Mob, Land, Email, Txt

I'm just receiving:

PK    ContactID  Tele    Mob   Land  Email  Txt
1       10        1       0     1     1      1
2       10        0       1     0     0      1
3       12        1       3     1     1      1

How do I modify to give me the desired results?

CodePudding user response：

If I understand correctly, you can try to use ROW_NUMBER window function to make it.

SELECT *
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY PK DESC) rn
    FROM Contacts
) t1
WHERE rn = 1

CodePudding user response：

The last by PK row for a ContactId using top with ties

SELECT top(1) with ties *
FROM Contacts
ORDER BY row_number() over(partition by ContactID order by PK desc)