0

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!

Lood
  • 1
  • 4
  • 2
    Use max(col2) and group by col1 – Markov Apr 23 '19 at 18:30
  • 1
    Have a look at [MAX (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-2017). – Thom A Apr 23 '19 at 18:31
  • Possible duplicate of [Select max value of each group](https://stackoverflow.com/questions/4510185/select-max-value-of-each-group) – ayorgo Apr 23 '19 at 18:50

1 Answers1

1

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
Thom A
  • 88,727
  • 11
  • 45
  • 75
reidh.olsen
  • 111
  • 3
  • but this would not keep the rest of the row right? If you wanted the whole row (with other non-duplicated values) that has the max value, this would not work – raquelhortab Feb 08 '23 at 08:16