SQL查询数据 求解（sql语句）

Question

一张表：table1（竖向3个值 为一条记录）
name：A, B, C, D, B , C ,D ,B ,C , D
other：a, b, c, d, e, f, g, h, i, j
No.: 1, 2, 3, 4, 2, 6, 2, 5, 5, 5 ,

我想查出 A, B, C, D
a, h , f , j
1, 5, 6, 5
name不重复且当name重复时取NO最大的那条记录

求SQL 高手指教 谢谢

Answer 1

先按name分组找出最大的no值及对应name，作为条件，查询所有列，语句如下：
SELECT *
FROM Temp_Tab
WHERE (NAME, No) IN (SELECT NAME,
MAX(No)
FROM Temp_Tab
GROUP BY NAME)
ORDER BY NAME;

Answer 2

select a.name,a.other,a.no
from table1 a
left join (
select name,max(no) no
from table1
group by name
) b
on a.name=b.name
and a.no=b.no

Answer 3

select b.name,b.other,b.[No.] from table1 b
join (select a.name,max(a.[No.]) Num from table1 a group by name) c
on b.name=c.name and b.[No.]=c.Num
order by b.name;

Answer 4

select name,other,no
from table1
inner join
(select name,max(no)
from table1
group by name) as table2
on table1.name = table2.name

Answer 5

select name,min(other)other,max(No)No from table1
group by name