sql server - SQL Query - Eliminating rows with logical conditions in SQL -

sql server query remove rows arithmetical logic. ms sql

eg:

id   name  varchar a1   b1      1 a1   b1      2 a2   b2      4 a2   b2      2 a3   b3      5 a3   b3      8 

expected output

a1   b1  1 a2   b2  2 a3   b3  5 

logic: need every id , name combination, least var_char value (unfortunately not int, have cast ).

please me in resolving this. tried working on many logic's nothing worked.

you can use row_number below:

select top (1) ties * yourtable    order row_number() over(partition id, [name] order convert(int,int_value) )  --you might require convert bigint if value bigger , in varchar 

or can using sub query:

select * (    select *, rown = row_number() over(partition id, [name] order int_value) yourtable )a a.rown = 1 

output below:

+----+------+-----------+ | id | name | int_value | +----+------+-----------+ | a1 | b1   |         1 | | a2 | b2   |         2 | | a3 | b3   |         5 | +----+------+-----------+ 

demo