一个sql语句

[ssniu1985]

SELECT min_cons_amount
FROM min_cons_history
WHERE min_cons_acct = 225280001
AND min_cons_effective_date <= to_date(20110101,'yyyymmdd')
and rownum=1
order by min_cons_effective_date desc


min_cons_history 表里min_cons_acct = 225280001 有多条 我现在要按min_cons_effective_date降序排列取第一条

上面的语句不能满足，因为rownum=1在order by 之前就取了。。。
好像要有个嵌套查询，但我忘记怎么写了。

[ssniu1985]

要先排序再取ROWNUM=1

[ychzxx]

select * from （SELECT min_cons_amount
FROM min_cons_history
WHERE min_cons_acct = 225280001
AND min_cons_effective_date <= to_date(20110101,'yyyymmdd')
order by min_cons_effective_date desc ) where rownum=1

[ojuju10]

select * from
( SELECT min_cons_amount
FROM min_cons_history
WHERE min_cons_acct = 225280001
AND min_cons_effective_date <= to_date(20110101,'yyyymmdd')

order by min_cons_effective_date desc
)
where rownum=1

[liusong_china]

SELECT min_cons_amount FROM
(SELECT min_cons_amount FROM min_cons_history
WHERE min_cons_acct = 225280001
AND min_cons_effective_date <= to_date(20110101,'yyyymmdd')
order by min_cons_effective_date desc) where rownum = 1

[ssniu1985]

SELECT min_cons_amount
FROM min_cons_history
WHERE ROWNUM =1 AND min_cons_amount=(
SELECT min_cons_amount
FROM min_cons_history
WHERE min_cons_acct = 225280001
AND min_cons_effective_date <= to_date(20110101,'yyyymmdd')
order by min_cons_effective_date desc)

这样为什么不行？

[ssniu1985]

我自己也想出来了。分给你们分了。

[zhaining522]

SELECT a.min_cons_amount
FROM min_cons_history a
WHERE 
              min_cons_effective_date = (select max(b.min_cons_effective_date)
                                         from min_cons_history b
                                         where b.min_cons_effective_date<=to_date(20110101,'yyyymmdd')
                                               AND b.min_cons_acct = 225280001
)