CODEWITHSUNDEEP
sqlsql-server
Lood
Lood

Reputation: 1

When duplicate in 1 column, get row with highest value

a Query results in something like the example below:

col1 col2
A    0
B    0
B    1
C    0
D    1

Now I want no duplicates in col1. but when col1 has duplicates it should give the row where col2 = 1 (or highest value) and don't give the row where col2 = 0. So the result will be:

col1 col2
A    0
B    1
C    0
D    1

How should this query look like?

EDIT:

max works, but there is a thirth colomn, with some text value when col2 = 1 How do I get there result on the right? So when there is duplicate in col1, then get the row where col2 = 1

col1 col2 col3                      col1 col2 col3
   A    0                              A    0   
   B    0                              B    1   XYZ   
   B    1   XYZ            -->         C    0   
   C    0                              D    1   YXA
   D    1   YXA

Thanks!

Upvotes: 0

Views: 2078

Answers (1)

reidh.olsen
reidh.olsen

Reputation: 111

Check out the MAX() function: https://learn.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-2017

SELECT col1,MAX(col2) 
FROM [yourTable] 
GROUP BY col1

Upvotes: 1

Related Questions