SQL求解

[Agile牧]

一张Item表
ID Name GROUP COST
1 iPhone IOS 8
2 iPad IOS 10
3 Samsung Android 10
4 Huawei Android 7
5 Xiaomi Android 6
6 Redhat Linux 7
7 Ubuntu Linux 8

如上所示，如何按GROUP查询，返回cost最大的item，结果要如下图所示
2 iPad IOS 10
3 Samsumg Android 10
7 Ubuntu Linux 8

[BOKYYY]

select * from Item where COST in (select max(COST) from Item group by [GROUP] )

[BOKYYY]

select * from Item a where COST in (select max(COST) from Item b where a.GROUP=b.GROUP group by [GROUP] ) 这样子的更精准

[Agile牧]

[Agile牧]

引用 2 楼 BOKYYY 的回复:

select * from Item a where COST in (select max(COST) from Item b where a.GROUP=b.GROUP group by [GROUP] ) 这样子的更精准

这个正解

[Agile牧]

引用 1 楼 BOKYYY 的回复:

select * from Item where COST in (select max(COST) from Item group by [GROUP] )

这个是不对滴

[非典型宅男c]

你是想要 order by 吧？

[PCCYC]

select id,name,group,cost from item, (select group gp,max(cost) maxcost from item group by group) where group=gp and cost =maxcost;

[xu176032]

select id, name, group, cost
  from (select id,
               name,
               group,
               cost,
               row_number() over(partition by group1 order by cost desc) fn
          from Item)
 where fn = 1