SQL Group by Max not desired results

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?

2 answers

  • answered 2022-05-04 10:28 D-Shih

    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
    

  • answered 2022-05-04 10:36 Serg

    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)
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum