group by 的简单问题

CREATE TABLE [dbo].[TestTable](
[c1] [varchar](10) NULL,
[c2] [int] NULL,
[c3] [numeric](10, 1) NULL
)

c1 c2 c3
A 5 5.4
A 6 2.4
B 5 6.5
B 6 7.2

select c1,c2,max(c3) from TestTable group by c1

我希望的结果是这样的:
A 5 5.4
B 6 7.2

结果确实这样的:
消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'testtable.c2' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

然后,你们懂得~
[最优解释]
更正一下

SELECT * 
FROM testtable a
WHERE EXISTS (SELECT 1 FROM (
select c1,max(c3) c3 from TestTable group by c1 ) 
b WHERE a.c1=b.c1 AND a.c3=b.c3)

[其他解释]

CREATE TABLE [dbo].[TestTable](
    [c1] [varchar](10) NULL,
    [c2] [int] NULL,
    [c3] [numeric](10, 1) NULL
)
INSERT TestTable
SELECT 'A',5,5.4 UNION ALL
SELECT 'A',6,2.4 UNION ALL
SELECT 'B',5,6.5 UNION ALL
SELECT 'B',6,7.2

--楼主的意思我认为是取c3列最大值的所有信息，而对于c2的大小不考虑
SELECT c1,c2,c3
FROM TestTable T1 WHERE NOT EXISTS (SELECT 1 FROM TestTable T2 WHERE T2.c1=T1.c1 AND T2.c3>T1.c3)

DROP TABLE TestTable



(4 行受影响)
c1         c2          c3
---------- ----------- ---------------------------------------
A          5           5.4
B          6           7.2

(2 行受影响)

[其他解释]
全部代码是

[其他解释]

SELECT *
FROM   (SELECT *,
               Row_number()OVER (partition BY c1 ORDER BY c3DESC ) AS id
        FROM   test_table) a
WHERE  id <= 1 

[其他解释]
select c1,c2,max(c3) from TestTable group by c1 ,c2


[其他解释]
select c1,c2,max(c3) from TestTable group by c1,c2
[其他解释]
该回复于2012-11-15 06:37:10被管理员删除
[其他解释]
4 楼 正解