急问一个sql语句？！

[ddnn]

表A： TDATE，id, *
表B： id, *

A和B靠id关联（A主B从），要求从A中取出100条数据按TDATE升序，并且将B中与A.id关联的所有数据取出

select B.* from B where id in
(select id from A where rownum<=100 order by tdate) -- "缺失右括号"

select B.* from A, B
where A.id=B.id
and A.rownum<=100 order by A.TDATE -- 不识别rownum

应该怎么写啊？

[zcs_1]

SELECT b.*
FROM b
WHERE id IN(
SELECT id
FROM
(SELECT id,row_number() (ORDER BY tdate) rn FROM a
)
WHERE rn <= 100
)

[xiaoxiao1984]

select b.* from b
where b.id in
(select id from (select * from a order by TDATE) where rownum <= 100 )

[Visual_Studio_Net]

楼上的，应该是row_number()over(order by tdate)吧

[ch8433]

select b.* from b where id in (
select id from (
select id, rownum rn from a order by tdate
) c where rn <= 100
)

[ch8433]

oracle有row_number()over()的语法么？