group by 后 max 纪录得不到？？？ 好像很有难度。

[jokerjava]

c1 c2 c3 c4

10 100 1000 888
11 88 1000 886
9 100 888 885

select max(c3) from a group by c2;
怎么能得到c3的这条纪录的c1,c2,c4

有主键能做么 c1为主健

没主键 又如何

[jokerjava]

c1 c2 c3 c4

10 100 1000 888
11 88 1000 886
9 100 888 885
8 100 1000 885

[HeavenHe]

select c1,c2,c3,c4 from a
where c3 in(select max(c3) from a group by c2)

[LGQDUCKY]

楼上说的对
select c1,c2,c4 from a
where c3 in(select max(c3) from a group by c2)

[dxj1113]

同意楼上2位.关于GROUP BY时怎么样得到非分组字段的值的问题有很多人问过，这个方法看来是比较好的一种

[suleen]

设表名为tbl
select a.c1,a.c2,a.c3
from tbl a,(select c2,max(c3) c3 from tbl group by c2) b
where a.c2 = b.c2 and a.c3 = b.c3

[beckhambobo]

select c1,c2,c3,c4 from a
where (c2,c3) in (select c2,max(c3) from a group by c2)

[jokerjava]

楼上的兄弟们的结果 不是我的本意 下面我仔细的描述一下
c1 c2 c3 c4

10 100 1000 888
11 88 1000 886
9 100 888 885
8 100 1000 885
12 100 1000 889

根据c2分组 得到c3为最大的随便一条纪录的全集
需求很简单 好像语句不怎么简单

select c2,max(c3) as c3 from a group by c2;

C2 C3
----- ----------
88 1000
100 1000

怎么能得到c3的这条纪录的c1,c4

我想要的结果
C1 C2 C3 C4
----- --------- ------ ----------
11 88 1000 886
10 100 1000 888
或

C1 C2 C3 C4
----- --------- ------ ----------
11 88 1000 886
8 100 1000 885

或
C1 C2 C3 C4
----- --------- ------ ----------
11 88 1000 886
12 100 1000 889

有主键能做么 c1为主健

没主键 又如何


这是我写的
select max(c1), a.c2, a.c3 from a, (select c2,max(c3) as c3 from a group by c2) b where a.c2 = b.c2 and a.c3 = b.c3 group by a.c2, a.c3;
能满足要求
不过太复杂了

那位兄弟能找一个简单点的方法

[welyngj]

想知道下面的兄弟的答案1

[bzszp]

我觉得语句没法再简单了。：）

[lqbok]

2楼的简单好用

[guangli_zhang]

select a.c1,a.c2,a.c3,a.c4 from table a
where (a.c2,a.c3) = (select max(a.c3) from table b where b.c1 = a.c1 group by b.c2)
group by a.c2

[guangli_zhang]

对不起上面错了
select a.c1,a.c2,a.c3,a.c4 from table a
where (a.c2,a.c3) = (select b.c2,max(b.c3) from table b where b.c1 = a.c1 group by b.c2)
group by a.c2