Filter results in select

select * from table

name1  4 o
name1  5 x
name1  3 x
name2  3 x
name3  2 x

I开发者_JAVA技巧 have above results in that name1 have values 3,4 and 5 I want to get the result as below where where the flag 'O' has been taken in case of multiple values for same name.here it is name1

name1  4 o
name2  3 x
name3  2 x

Please help!

You could use row_number to select the lowest value of the ox column, like:

select  *
from    (
        select  *
        ,       row_number() over (partition by name order by ox asc) as rn
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

The subquery is required because you can't use row_number directly in a where clause.