select id,index,max(usedtimes)  as Max_usedtimes from table group by index

------解决方案--------------------
SQL code
select id,[index],max(usedtimes)  as Max_usedtimes from tb group by [index]

------解决方案--------------------
楼上的代码能跑吗？group by index以后还能选出来ID?

------解决方案--------------------
探讨


SQL code
消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'tb.usedtimes无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。


------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[index] varchar(4),[usedtimes] int)
insert [tb]
select 1,'a-01',1 union all
select 2,'a-01',2 union all
select 3,'a-02',1 union all
select 4,'a-03',1 union all
select 5,'a-03',2 union all
select 6,'a-04',1 union all
select 7,'a-04',2
go

select * 
from tb t
where not exists(select 1 from tb where [index]=t.[index] and usedtimes>t.usedtimes)

/*
id          index usedtimes
----------- ----- -----------
2           a-01  2
3           a-02  1
5           a-03  2
7           a-04  2

(4 行受影响)

*/

------解决方案--------------------
SQL code
--or
select * 
from tb t
where usedtimes=(select max(usedtimes) from tb where [index]=t.[index])
order by id
--or
select * 
from tb t
where usedtimes=(select top 1 usedtimes from tb where [index]=t.[index] order by usedtimes desc)
order by id

/*
id          index usedtimes
----------- ----- -----------
2           a-01  2
3           a-02  1
5           a-03  2
7           a-04  2

(4 行受影响)

*/

------解决方案--------------------
怎么不看我的
select id,index,max(usedtimes) over(partition by index) as Max_usedtimes from table

------解决方案--------------------
然后再和原表join 一下就行了
with cte as(
select id,index,max(usedtimes) over(partition by index) as Max_usedtimes from table)
select table.* from cte join table
on cte.index=table.index and cte.Max_usedtimes =table.usedtimes

------解决方案--------------------
/*
id index usedtimes
1 a-01 1
2 a-01 2
3 a-02 1
4 a-03 1
5 a-03 2
6 a-04 1
7 a-04 2
我要取出每条数据中usedtimes最大的一条数据，要是就一条，那么就取他
结果：
id index usedtimes
2 a-01 2
3 a-02 1
5 a-03 2
7 a-04 2
*/

go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id varchar(2),
[index] varchar(10),
usedtimes int
)
go
insert tbl
select '1','a-01',1 union all
select '2','a-01',2 union all
select '3','a-02',1 union all
select '4','a-03',1 union all
select '5','a-03',2 union all
select '6','a-04',1 union all
select '7','a-04',2


select id,tbl.[index],a.max_usedtimes from tbl  
inner join(select [index],MAX(usedtimes) as max_usedtimes from tbl group by [index])a