SQL Return Unique Data

Question

I have a table that looks like this:

id,  col1,   col2   col3
0    "Goat"  "10"   "0"
1    "Cat"   "11"   "0"
2    "Goat"  "12"   "1"
3    "Mouse" "13"   "0"
4    "Cat"   "14"   "2"

I want to be able to return UNIQUE values ​​in Col1. And if there are two identical values ​​in col1, then use col3 to decide which value to use if it has a value of "0" in col3.

So, I should get the table as follows:

id,  col1,   col2   col3
0    "Goat"  "10"   "0"
1    "Cat"   "11"   "0"
3    "Mouse" "13"   "0"

Hope this makes sense?

thanks

Answer 1

More on ROW_NUMBER () OVER (): http://msdn.microsoft.com/en-us/library/ms186734.aspx

You need to select the rows where ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col3) is 1:

select *
from 
(select
  id, 
  col1, 
  col2, 
  col3,
  ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col3) nom
from table_name) a
where a.nom = 1

Answer 2

, col3 col1:

select t.*
from @t t
inner join (
    select col1, mincol3 = min(col3)
    from @t
    group by col1
) filter
    on t.col1 = filter.col1
    and t.col3 = filter.mincol3

(col1, col3), .

:

declare @t table (id int, col1 varchar(max), col2 varchar(max), 
    col3 varchar(max))
insert into @t
select 0,    'Goat', '10',  '0'
union select 1,    'Cat',  '11',  '0'
union select 2,    'Goat', '12',  '1'
union select 3,    'Mouse','13',  '0'
union select 4,    'Cat',  '14',  '2'