SQL高手再进:有关分组后取TOP N条记录的问题

Question

service_name      read_date               content
1        DB        20060707 14:29:26           .........    //取这条
2        DB        20060707 14:29:22           .........    //取这条
3        DB        20060707 14:29:18                        //取这条
4        DB        20060707 14:29:14                        //取这条
5        DB        20060707 14:29:09                        //取这条
6        DB        20060707 14:29:05
7        DB        20060707 14:29:01
8        DB        20060707 14:28:57
9        DB        20060707 14:28:53
10        DB        20060707 14:28:49
11        DB        20060707 14:27:40
12        DB        20060707 14:27:36
13        DB        20060707 14:27:32
14        WEB        20060706 16:36:14                    //取这条
15        WEB        20060706 16:36:07                    //取这条
16        WEB        20060706 16:36:00                    //取这条
17        WEB        20060706 16:35:53                    //取这条
18        WEB        20060706 16:35:46                    //取这条
19        WEB        20060706 16:35:39
20        WEB        20060706 16:35:32
21        WEB        20060706 16:35:25
22        WEB        20060706 16:35:18
23        WEB        20060706 16:35:11
24        WEB        20060706 16:35:04
25        WEB        20060706 16:34:57已经分组排过序的表, 如上. 现在要取以service_name分组的read_date最大的TOP 5条记录, 即上面做标记的记录, 而且service_name, read_date,   content这三个字段都要有, 该怎么来写这个SQL? 感谢!!!

Answer 1

select service_name,read_date,content from (select service_name,read_date,content, rank() over(partition by service_name order by read_date desc) id from table where id <= 5) a;

Answer 2

select service_name,read_date,content from tab_name where rowid in 
(select rid from (
  (select rowid as rid, rank() over(partition by service_name order by read_date desc) id from tab_name) 
where id <= 5)) ;

Answer 3

排序以后最后加一句 where rownum<5

Answer 4

select service_name,read_date,content from table where rownum < 6
union all
(
select service_name,read_date,content from table where rownum <= 18
minus
select service_name,read_date,content from table where rownum < 14
)