SQL Return Unique data

Question

Hi,

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 be able to return the UNIQUE values in Col1 AND if there are two identical values in col1 then use col3 to decide which value to use i.e. if it has a '0' in col3.

So I should get a table like this:

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

Hope this makes sense?

Thank you

Answer 1

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

You have to select 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

You could use an inner join to filter the rows which have to lowest col3 for their 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

If there are multiple rows with the same (col1,col3) this query will return them all.

Code segment to generate test data:

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'