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
  • 3
  • 2
    Use max(col2) and group by col1 – Markov Apr 23 '19 at 18:30
  • 1
    Have a look at [MAX (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-2017). – Larnu 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://docs.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-2017

SELECT col1,MAX(col2) 
FROM [yourTable] 
GROUP BY col1
Larnu
  • 76,706
  • 10
  • 34
  • 63
reidh.olsen
  • 111
  • 3