select * from table_name where rev_id in (select max(rev_id) from table_name group by dwg_id)
解决方案 »
- oracle job问题
- Oracle有没有备份的文件损坏原来不起的情况?多吗?
- 求助,oracle obdb driver for linux 驱动 liboraodbc.so ???
- 高手请进:怎样使用wrap工具加密字符串常量?
- 如何用SQL 语句实现如下. 横纵转制的结果(超难)
- 大家用oracle9i,还是oracle10g多呀,
- 这个sql我不明白?
- 请教,如何更好的处理字符长的字段?
- 重发
- 第一次用oracle:为什么我无法登陆到Enterprise Manager?
- oracle
- oracle 求sql,表中有个字段companyName,这个字段中的值如果有相似值则更新为相同值
select 42723 dwg_id,9 rev_id,'1' rev_nv from dual union all
select 42723,8,'0c' from dual union all
select 42769,0,'#' from dual)
select dwg_id, rev_id, rev_nv
from (select dwg_id,
rev_id,
rev_nv,
RANK() OVER(partition by dwg_id order by rev_id desc) px
from test)
where px = 1
这样子出来结果还是dwg_id会有重复的,不太对好像,不过还是谢谢哦!
SELECT 42723 DWG_ID,9 REV_ID,'1' REV_NC FROM DUAL UNION ALL
SELECT 42723,7,'0C' FROM DUAL UNION ALL
SELECT 42723,9,'0A' FROM DUAL UNION ALL
SELECT 42723,2,'0' FROM DUAL UNION ALL
SELECT 42723,1,'L' FROM DUAL UNION ALL
SELECT 42723,8,'0D' FROM DUAL UNION ALL
SELECT 42723,6,'0B' FROM DUAL UNION ALL
SELECT 42723,0,'#' FROM DUAL UNION ALL
SELECT 42723,2,'M' FROM DUAL UNION ALL
SELECT 42723,3,'M1' FROM DUAL UNION ALL
SELECT 42769,0,'#' FROM DUAL
)SELECT A.*,B.REV_NC FROM T_test B,(SELECT T.DWG_ID,MAX(T.REV_ID) REV_ID FROM T_TEST T GROUP BY T.DWG_ID) A
WHERE A.DWG_ID=B.DWG_ID(+) AND A.REV_ID=B.REV_ID
存在两个相同最大值的时候都会给查出来,不知道符不符合你要求
这样子出来结果还是dwg_id会有重复的,不太对好像,不过还是谢谢哦!
select * from table_name where rev_id||dwg_id in (select max(rev_id)||dwg_id from table_name group by dwg_id);
select t1.dwgid, t1.revid, t1.revnc
from test t1,
(select max(revid) revid, dwgid from test group by dwgid) t2
where t1.dwgid = t2.dwgid
and t1.revid = t2.revid;
max(t.rev_id) keep(dense_rank last order by t.rev_id) over(partition by t.dwg_id) as max_rev_id,
t.rev_nc
from 表 t
select t1.dwgid,t1.revid,t1.rev from T_Test t1
inner join
(
select t.dwgid,max(t.revid) max_revid from T_Test t
group by t.dwgid
) t2 on t1.dwgid=t2.dwgid and t1.revid=t2.max_revid
select * from test_tabale where (Dwg Id,Rev Id) in (select distinct Dwg Id,Rev Id from test_tabale )希望有帮助
select * from test_tabale where (Dwg Id,Rev Id) in (select distinct Dwg Id,max(Rev Id) from test_tabale group by Dwg Id )