sql如何筛选出离今天最近的日期

[fs82619299]

sql语句为

select distinct tr.id,
s.frate,
s.dteffective

from sett_transopenfixeddeposit tr,
sett_interestrate s

where tr.nnoticeday = s.nnotifytypeid


查询出的结果为：

ID FRATE DTEFFECTIVE
6439 1.62 2004-1-1
6439 1.88 2011-5-25
6441 1.08 2004-1-1
6441 3.2 2011-1-1

这里ID有重复的行，我需要取出列DTEFFECTIVE 距离今天最近的那条数据
就是期望的结果应该是

ID FRATE DTEFFECTIVE
6439 1.88 2011-5-25
6441 3.2 2011-1-1

请问sql语句该如何修改。

[秋雨飘落]

select distinct tr.id, s.frate, s.dteffective

from sett_transopenfixeddeposit tr,
( select *
from sett_interestrate s
where s.DTEFFECTIVE =
(select max(DTEFFECTIVE)
from sett_interestrate d
where s.nnotifytypeid = d.nnotifytypeid) s

where tr.nnoticeday = s.nnotifytypeid

[BlueskyWide]

手边没有工具，可以试一下：

select distinct tr.id,
s.frate,
s.dteffective
from sett_transopenfixeddeposit tr,
sett_interestrate s
where tr.nnoticeday = s.nnotifytypeid and s.dteffective in (
select max(s.dteffective) from sett_transopenfixeddeposit tr,
sett_interestrate s
where tr.nnoticeday = s.nnotifytypeid);

[fs82619299]

2L 缺失右括号

3L 我试下行不行

谢谢拉~

[fs82619299]

3L 还是不行啊

你的语句查出的结果只有一个了
ID FRATE DTEFFECTIVE
6439 1.88 2011-5-25

[fs82619299]

2L
我把括号加上了

正在试试

[gelyon]

--分析函数很简单的
select id,frate,dteffective from (
      select id,frate,dteffective, 
            row_number()over(partition by id order by dteffective desc) rn 
      from 
          (select distinct tr.id,s.frate,s.dteffective 
           from sett_transopenfixeddeposit tr,sett_interestrate s
           where tr.nnoticeday = s.nnotifytypeid )
)
where rn=1;

[fs82619299]

2L 的可以用哈^_^

6L的我看不太懂···
row_number()over(partition 这几个是什么···