与最大值选择行

Question

我已经一个表T1与下面列

C1 SeqNo C2 ID 

1 1  10 abc 
1 1  20 xyz 
1 1  30 mn0 
1 2  10 123 
1 3  10 abc 
1 3  20 xyz 
2 1  10 a1c 
2 2  10 x1z 
2 2  20 m10 
2 2  30 1k3 
2 3  10 a1c 
2 3  20 x1z 

我必须选择ID其中SeqNo是max和C2为C1

的每个不同的值最大 seqNo的最大

的结果应该是：

1 3 20 xyz 
2 3 20 x1z 

如果有人可以帮助请

Answer 1

所有你需要的是一个GROUP BY子句和MAX聚合函数：

SELECT id, MAX(rev) FROM YourTable GROUP BY id ...

Answer 2

SELECT C1, SeqNo, MAX(C2), ID 
    FROM T1 
WHERE C1 = 1 
GROUP BY SeqNo 
ORDER BY SeqNo DESC 
LIMIT 1; 

Answer 3

在DB2

select C1, ID, Max(SeqNo) as SeqNo, Max(C2) as C2 
from yourtable 
where C1=1 
group by C1, ID 
order by 3 desc, 4 desc, 2 desc 
fetch first rows only 

Answer 4

SELECT * FROM T1 其中C2 > =（SELECT max（c2）FROM check） limit 1

Answer 5

在DB2

select * from (
select f1.*, rownumber() over(order by SeqNo desc, C2 desc, ID desc) rang 
from yourtable f1 where f1.C1=1 
) tmp where tmp.rang=1