Pfthb

I want to select if one row where multiple columns are the same. For example:

col1 col2 col3 col4

 a b 1 2
 b b 1 2
 a c 1 2
 b b 1 3
 a c 2 1

Condition: Select only if values of columns (col1, col2, col3) are different from other rows and value of col4 is max of rows which are the same.

For example expected Output is:

 a b 1 2
 b b 1 3
 a c 1 2
 a c 2 1

Mandatory NOT EXISTS solution... your condition written as a not exist query:

DECLARE @t TABLE (col1 varchar(100), col2 varchar(100), col3 int, col4 int);
INSERT INTO @t VALUES
('a', 'b', 1, 2),
('a', 'c', 1, 2),
('a', 'c', 2, 1),
('b', 'b', 1, 2),
('b', 'b', 1, 3);

SELECT *
FROM @t AS t
WHERE NOT EXISTS (
 SELECT 1
 FROM @t AS dup
 WHERE dup.col1 = t.col1
 AND dup.col2 = t.col2
 AND dup.col3 = t.col3
 AND dup.col4 > t.col4 -- outer row has smaller col4
)

Demo on DB Fiddle

Yes possible, just use group by with max aggregation as

with tab(col1,col2,col3,col4) as
(
 select 'a','b',1,2 union all
 select 'b','b',1,2 union all
 select 'a','c',1,2 union all
 select 'b','b',1,3 union all
 select 'a','c',2,1 
)
 select col1, col2, col3, max(col4) as col4
 from tab
 group by col1, col2, col3;

col1 col2 col3 col4
 a b 1 2
 a c 1 2
 a c 2 1
 b b 1 3

Rextester Demo