排名

表A——（A_id是选项的ID，title是选项的标题，num是这个选项所获得的票数）
A_id     title     num
1        吃饭     80
2        睡觉     75
3        看书     80
4        上网     92
5        游戏     70

表B——（每一条投票的具体信息，A_id是前面选项的ID，date是投票时的时间，istrue是这条投票是否有效）    
B_id    A_id          date                istrue
1        2      2006-8-27 11:42:08          是
2        2      2006-8-27 11:37:24          否
...

现在想统计出票数最多并且有效的前3个选项，按一、二、三等奖排名次，就比如表A的4，1，3这3条记录。但是有一个问题，其中1和3票数相同，同是80票，现在想让谁最先到80票，谁就得第2。请问这个SQL语句该怎么写，大家帮帮忙吧，谢谢了~~

create table A(A_id int,title varchar(10),num int)
insert A
select 1        ,'吃饭',80 union all
select 2        ,'睡觉',75 union all
select 3        ,'看书',80 union all
select 4        ,'上网',92 union all
select 5        ,'游戏',70
--select * from A

create table B(B_id int,A_id int,date datetime,istrue char(2))
insert B
select 1,2,'2006-8-27 11:42:08','是' union all
select 1,1,'2006-8-25 11:42:08','是' union all
select 1,3,'2006-8-24 11:42:08','是' union all
select 1,3,'2006-8-23 11:42:08','是' union all
select 2,2,'2006-8-27 11:37:24','否'
--select * from B

select top 3
A.*,
排名 = (
select count(*)
from A aa
where aa.num > a.num or
aa.num=a.num and isnull((select max(date) from b where a_id=aa.a_id and istrue = '是'),0)>=isnull(b.date,0)
),
B.date
from A
left join(
select A_id, date = max(date)
from B
where istrue = '是'
group by A_id
)b
on a.A_id = b.A_id
order by 排名
go

drop table A,B

-- 结果:
A_id        title      num         排名          date
----------- ---------- ----------- ----------- -----------------------
4           上网         92          1           NULL
1           吃饭         80          2           2006-08-25 11:42:08.000
3           看书         80          3           2006-08-24 11:42:08.000

(3 行受影响)
select ID,title,num from (
 select b.A_id ID,a.title title,count(distinct b.A_id) num,max(b.date)
 from A a,B b
 where a.A_id = b.A_id and b.istrue = '是'
 group by b.A_id
 order by count(distinct b.A_id) desc,max(b.date) asc
) where rownum < '4';

select top 3
A.*,
排名 = (
select count(*)
from A aa
where aa.num > a.num or
aa.num=a.num and isnull((select max(date) from b where a_id=aa.a_id and istrue = '是'),0)<=isnull(b.date,0)  -- 先回先排的话, 改这里面为<=
),
B.date
from A
left join(
select A_id, date = max(date)
from B
where istrue = '是'
group by A_id
)b
on a.A_id = b.A_id
order by 排名

select top 3 a.A_id from a inner join b on a.A_id=b.A_id order by num desc,date desc