1.我要查出最新的两条数据 以 group_id 降序, group_id可能重复,比如有两个group_id都是3只需取一个就行 表
create table mdsets_question_master
(
group_id number,
question_id varchar2(20) not null, 主键自增
question_title varchar2(100),
question_type varchar2(1),
creator varchar2(20) ,
creation_date date,
updater varchar2(20),--default user_name not null,
update_date date,
constraint mdsets_question_master_pk primary key(question_id) )
下面是测试数据
2 7 aaa 2 sys 14-12-10 sys 14-12-10
1 5 bbb 2 sys 14-12-10 sys 14-12-10
1 6 ccc 2 sys 14-12-10 sys 14-12-10
2 8 ddd 2 sys 14-12-10 sys 14-12-10
3 13 eee 1 sys 14-12-10 sys 14-12-10
3 12 fff 2 sys 14-12-10 sys 14-12-10
4 12 fff 2 sys 14-12-10 sys 14-12-10
create table mdsets_question_master
(
group_id number,
question_id varchar2(20) not null, 主键自增
question_title varchar2(100),
question_type varchar2(1),
creator varchar2(20) ,
creation_date date,
updater varchar2(20),--default user_name not null,
update_date date,
constraint mdsets_question_master_pk primary key(question_id) )
下面是测试数据
2 7 aaa 2 sys 14-12-10 sys 14-12-10
1 5 bbb 2 sys 14-12-10 sys 14-12-10
1 6 ccc 2 sys 14-12-10 sys 14-12-10
2 8 ddd 2 sys 14-12-10 sys 14-12-10
3 13 eee 1 sys 14-12-10 sys 14-12-10
3 12 fff 2 sys 14-12-10 sys 14-12-10
4 12 fff 2 sys 14-12-10 sys 14-12-10
解决方案 »
- 不知道有没有免费的ORACLE?
- 一句SQL得到 集合列表中的数据自动组合
- 几个ORACLE分布数据库合并到一个ORACLE数据库
- exp导出数据时,实现导出所有的表结构,并导出部分基础表的数据。
- blob字段显示问题
- Oracle9i 的备份与恢复(exp/imp)
- 大家帮我理解这一句
- 菜鸟问:在创建视图(view)的语句里面,可以使用用户自己开发的函数或者存储过程吗?
- 郁闷啊~~~~~~~~~安装Oracle9i的问题!各位兄弟帮帮我,谢谢了
- 紧急求救,表被锁了,怎么去解除?
- 把wf_engine包赋予一个用户
- fetch cursor into record后,有对record循环的方法吗
可以先按group分组,查出每个group里question_id的最大值,然后把查询结果和原始记录做个内连接就行了
select r.*,rownum rn from mdsets_question_master r order by r.group_id desc
)
where rn <=2
creator,creation_date,updater,update_date
from (select group_id ,question_id ,question_title ,question_type ,
creator,creation_date,updater,update_date,
row_number() over(partition by group_id order by update_date desc) rn
from mdsets_question_master)
where rn=1
(select row_number() over(partition by group_id order by question_id desc) rn,a.*
from mdsets_question_master a
order by group_id desc)
where rn = 1;
WHERE question_id IN
(SELECT MAX(question_id) keep(DENSE_RANK last ORDER BY creation_date) over(partition by group_id)
FROM mdsets_question_master)