How to select distinct elements when they appear repeated in a table? (T-SQL)

Question

Consider the following table:

create table temp
(                  
 name int,
 a int,
 b int
)

insert into temp (name, a, b)
values (1, 2, 3)

insert into temp (name, a, b)
values (1, 4, 5)

insert into temp (name, a, b)
values (2, 6, 7)

I want to select *(all fields) with distinct [name]. In the case of two or more rows having the same [name], to choose whether to display the first (1, 2, 3) or the second row(1, 4, 5) the rule can be to choose the one with greater [b].

Can you point how must I write this stored procedure?

Answer 1

Here is one way to do this sql 2000 and up version

select t1.* from(
select name,max(B) as MaxB
from temp
group by name) t2 
join temp t1 on t1.a = t2.MaxB
and t1.name = t2.name

SQL 2005 and up version

select name, a, b
from (
select m.*,
row_number() over (
partition by name
order by B desc) as rn
from temp m
) m2
where m2.rn = 1;

Answer 2

SELECT t.*
FROM temp t
INNER JOIN (
  SELECT name, max(b) as b
  FROM temp
  GROUP BY name
) m
ON t.name = m.name
AND t.b = m.b

Not exactly fast on big tables, unless you have an index on name, b.

Answer 3

In MSSQL 2005 and above:

SELECT name, a, b
FROM (
    SELECT temp.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY b DESC) AS rn
    FROM   temp
) t
WHERE rn = 1

Answer 4

CREATE VIEW Max_B_From_Temp AS
SELECT name,Max(b) as b
FROM temp
GROUP BY name

SELECT temp.* 
FROM temp INNER JOIN Max_B_From_Temp 
ON 
    temp.name=Max_B_From_Temp.name and 
    temp.b=Max_B_From_Temp.b

Its not a stored procedure, but you can call the query from one.