'Select unique rows based on only some columns

I have an SQL view, and I want to get unique rows based on only some columns.

  1. If all other columns are the same (excluding A), duplicates must be deleted. and
  2. If all other columns are the same (excluding B), duplicates must be deleted.

I used the code below, but it still gives me the max of A then the Max of B, and what I want is to get distinct rows excluding these two columns, and I want to keep these columns in the select function.

SELECT Name, Account, MAX(A), MAX(B)
FROM MyTable
GROUP BY Name, Account

Thanks in advance!

sqlsql-server


Solution 1:[1]

Have not tested, but is this what you are searching for?

select sub.Name,sub.Account,MAX(sub.prod) 
  from 
(
   select Name,Account,MAX(A) as prod from MyTable group by Name,Account
   union all
   select Name,Account,MAX(B) as prod from MyTable group by Name,Account
) as sub
group by sub.Name, sub.Account

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 hogni89

Related Questions