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?
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
how many words do you know
Powered by Examplum