c***n
发帖数: 921 | 1 【 以下文字转载自 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 | H*M
发帖数: 1268 | 2 ?
why cant you
select A1, A2, value, max(date)
from table
group by A1, A2, value
directly?
Did i miss something?
【在 c***n 的大作中提到】
: 【 以下文字转载自 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.
| c***n
发帖数: 921 | 3 If I run the logic you provided, it may return following result:
A1, A2, value, date | c***n
发帖数: 921 | 4 这样语法会出错。 if you put value in the select clause, you have to put it
in the group by too. | H*M
发帖数: 1268 | 5 不太清楚你的意思.你想在库中到max(date)的那一行,return那一行?
select *
from table1
where table1.date =
( select max(date) from table2)
【在 c***n 的大作中提到】
: 这样语法会出错。 if you put value in the select clause, you have to put it
: in the group by too.
| H*M
发帖数: 1268 | 6 语法错,
select list必须 全部出现在grouby-list中,除了aggregate functions. | c********g
发帖数: 449 | 7 my bad:)
how about this:
select t1.a1, t1.a2, t1.value
from table as t1
where date = (select max(date) from table as t2 where t1.a1=t2.a1 and t1.a2=
t2.a2);
maybe run slow:( | A*****i
发帖数: 465 | 8 Why not use procedure?
Create proc retrieveMaxDate @maxday out
as
set @maxday = (select max(date) from table);
declare @maxday datetime;
exec retrieveMaxDate maxday out
select A1, A2, value, date from table where date = @maxday | s**m
发帖数: 1564 | 9 try this and let me know if it works well.
select a1, a2, value, row_number() over(partition by a1, a2 order by date
desc) as 'rownumber'
from table
where 1= rownumber | c***n
发帖数: 921 | 10 a11,a21,v1, 20090221
a11,a21,v2, 20090320
a21,a21,v3, 20090621
a21,a21,v4, 20090820
不同的A1,A2 combination may have different max(date).
In above case, I am expecting to return
a11,a21,v2, 20090320
a21,a21,v4, 20090820
【在 H*M 的大作中提到】
: 不太清楚你的意思.你想在库中到max(date)的那一行,return那一行?
: select *
: from table1
: where table1.date =
: ( select max(date) from table2)
| | | i****l
发帖数: 135 | 11 现在没机器验证，但感觉这个应该是对的
【在 s**m 的大作中提到】
: try this and let me know if it works well.
: select a1, a2, value, row_number() over(partition by a1, a2 order by date
: desc) as 'rownumber'
: from table
: where 1= rownumber
| q**y
发帖数: 135 | 12 就用windowing function就行了。 | x****r
发帖数: 17 | 13 也可以试试索引，估计会快不少，当然， 是否适合， 取决于具体应用了 | c***n
发帖数: 921 | 14 This is the final code that runs. Thanks for the idea! As the dabase is db2
Syste i, the SQL grammer may be a little bit different from other databases
.
select a1,a2,value from (
select a1, a2, value, row_number() over(partition by a1, a2 order by date
desc
) as rnber
from table ) end
where rnber=1 ;
【在 s**m 的大作中提到】
: try this and let me know if it works well.
: select a1, a2, value, row_number() over(partition by a1, a2 order by date
: desc) as 'rownumber'
: from table
: where 1= rownumber
| c***n
发帖数: 921 | 15 the problem has been solved. Thank you guys.
【在 c***n 的大作中提到】
: This is the final code that runs. Thanks for the idea! As the dabase is db2
: Syste i, the SQL grammer may be a little bit different from other databases
: .
: select a1,a2,value from (
: select a1, a2, value, row_number() over(partition by a1, a2 order by date
: desc
: ) as rnber
: from table ) end
: where rnber=1 ;
|
|
