求一sql语句

[fionazou]

数据是这样的:

abc 2.0 0.222
abc 1.0 0.333
abc 0.3 1.236
aaa 3.0 0.363
aaa 2.3 0.963

希望得到如下的记录(也就是最后一列是最大的):

abc 0.3 1.236
aaa 2.3 0.963

[召集令]

abc 1.0 1.236
abc 0.3 1.236
-------------------
这两行 的 最后一列 一样的 怎么选择?

[jackeyabc]

CREATE TABLE tablename(one varchar(10), two decimal(6,3), three decimal(6,3))
INSERT INTO tablename
SELECT 'abc', 2.0, 0.222
UNION ALL SELECT 'abc', 2.0, 0.222
UNION ALL SELECT 'abc', 1.0, 1.236
UNION ALL SELECT 'abc', 0.3, 1.236
UNION ALL SELECT 'aaa', 3.0, 0.363
UNION ALL SELECT 'aaa', 2.3, 0.963

select * from
(select * from tablename a where (select count(*) from tablename b where b.one=a.one and b.three>a.three)=0 ) aa
where (select count(*)+1 from tablename bb where bb.one=aa.one and bb.two>aa.two and bb.three=aa.three)=1

--abc 1.0 1.236
--aaa 2.3 0.963



DROP TABLE tablename



--------------------------
one two three
abc 1.000 1.236
aaa 2.300 .963

[hhhdyj]

SELECT COL1, MAX(COL2) AS COL2, COL3 FROM
(SELECT * FROM TABLENAME T WHERE NOT EXISTS(SELECT 1 FROM TABLENAME WHERE COL1 = T.COL1 AND COL3 > T.COL3))TT
GROUP BY COL1, COL3

[召集令]

如果按第一列分组前提下,最后一列没有重复的:

select a.*
from 表名 as a,
(select 列1 , max(最后一列) as 最后一列 group by 列1) as b
where a.列1=b.列1 and a.最后一列=b.最后一列

[fionazou]

有没有简单一点的,只用一个SELECTT就可以的??

[hhhdyj]

SELECT * FROM TABLENAME T WHERE NOT EXISTS(SELECT 1 FROM TABLENAME WHERE COL1 = T.COL1 AND COL3 > T.COL3)

[fionazou]

你上面的方法如果是下面的数据就有重复的了.

数据是这样的:

abc 2.0 0.222
abc 1.0 0.236
abc 0.3 1.236
aaa 3.0 0.363
aaa 2.3 0.963

希望得到如下的记录(也就是最后一列是最大的):

abc 1.0 1.236
aaa 2.3 0.963

[hhhdyj]

CREATE TABLE tablename([col1] varchar(10), [col2] decimal(6,3), [col3] decimal(6,3))
INSERT INTO tablename
SELECT 'abc', 2.0, 0.222
UNION ALL SELECT 'abc', 1.0, 0.236
UNION ALL SELECT 'abc', 0.3, 1.236
UNION ALL SELECT 'aaa', 3.0, 0.363
UNION ALL SELECT 'aaa', 2.3, 0.963

SELECT * FROM TABLENAME T WHERE NOT EXISTS(SELECT 1 FROM TABLENAME WHERE COL1 = T.COL1 AND COL3 > T.COL3)

DROP TABLE tablename

--结果
col1 col2 col3
---------- -------- --------
abc .300 1.236
aaa 2.300 .963

[fionazou]

不好意思,数据是这样的:
abc 2.0 0.222
abc 1.0 1.236
abc 0.3 1.236
aaa 3.0 0.363
aaa 2.3 0.963

希望得到如下的记录(也就是最后一列是最大的):

abc 1.0 1.236
aaa 2.3 0.963