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 之前就取了
好像要有个嵌套查询,但我忘记怎么写了。
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 之前就取了
好像要有个嵌套查询,但我忘记怎么写了。
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
( 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
(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
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)这样为什么不行?
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
)