c***n
发帖数: 921 | 1 a table fctTbl contains following fields: A1, A2, date, value; the primary
key is . The example "date" value is 20090520 (May 20 2009),
20090601 ....
fctTble has 1 millon rows in it.
The question is, how to get the result set with coloumn A1,A2,value, where
the maximum date rows are returned.
Following code is my solution, and it takes forever to run. I want to ask
whether you have better ideas.
select t1.A1,t1.A2,t2.value from
(select A1,A2,max(date) as date from fctTbl group by A1 |
|
c***n
发帖数: 921 | 2 【 以下文字转载自 Database 讨论区 】
发信人: citan (yoyo), 信区: Database
标 题: How to design the sql for this problem?
发信站: BBS 未名空间站 (Thu Dec 10 16:22:40 2009, 美东)
a table fctTbl contains following fields: A1, A2, date, value; the primary
key is . The example "date" value is 20090520 (May 20 2009),
20090601 ....
fctTble has 1 millon rows in it.
The question is, how to get the result set with coloumn A1,A2,value, where
the maximum date rows are returned.
Following code is my solution, and it takes f |
|
c***n
发帖数: 921 | 3 Thanks! I come up with this finally.
select a1,a2,value from (
select a1,a2,value row_number() over(partition by a1,a2 order by date
desc
) as rnber
from fctTbl ) end
where rnber=1 ; |
|
