表A
NAME DATE COL1
------------------------------
A 2011/06/07 4
A 2011/06/09 6
A 2011/06/03 7
B 2011/06/29 5
B 2011/06/30 6
求查询出每个NAME下的日期最大的结果
NAME DATE COL1
------------------------------
A 2011/06/09 6
B 2011/06/30 6
自己写的语句感觉不效率select m."name",m."date",b.col1 from
(select a."name", MAX(a."date") "date" from tb_test_only a group by "name")m
left join tb_test_only b
on m."name" =b."name" and m."date" =b."date"
NAME DATE COL1
------------------------------
A 2011/06/07 4
A 2011/06/09 6
A 2011/06/03 7
B 2011/06/29 5
B 2011/06/30 6
求查询出每个NAME下的日期最大的结果
NAME DATE COL1
------------------------------
A 2011/06/09 6
B 2011/06/30 6
自己写的语句感觉不效率select m."name",m."date",b.col1 from
(select a."name", MAX(a."date") "date" from tb_test_only a group by "name")m
left join tb_test_only b
on m."name" =b."name" and m."date" =b."date"
解决方案 »
- oracle11g ORA-01034:oracle not available ORA-27101:shared memory realm does not
- 求助 oracle 数据备份 急急急!!!
- sql语句问题,请进(在线)
- 数据库临时表的问题
- 请问这个SQL能实现吗? 又该如何写?
- UTL_FILE
- 谁能提供一个oracle9.0.2或以上版本的下载地址?(非官方网站)
- 救命啊!装了两个库就出错了。
- sos!一个存储过程的问题
- 看来我的学习能力的确太弱了...装个g10datebase花了一下午时间到最后一步还卡住了.(真是受折磨了)
- ORACLE10新手一篇入门
- 关于oracle date数据类型
FROM a
WHERE NOT EXISTS (SELECT *
FROM a b
WHERE b."name" = a."name"
AND b."date" > a."date");
select name,date,col1 from (
select name,date,col1,row_number() over (partition by name order by date desc) rn)
where rn=1
NAME DATE COL1
------------------------------
A 2011/06/07 4
A 2011/06/09 6
A 2011/06/03 7
B 2011/06/29 5
B 2011/06/30 6
求查询出每个NAME下的日期最大的结果
NAME DATE COL1select * from a a where a.date = (select max(b.date) from a b where a.name = b.name)
select distinct t.name, max(t.date) over(partition by t.name)
from table_test t
--方法1 (NOT EXISTS)
SELECT *
FROM emp a
WHERE NOT EXISTS (SELECT *
FROM emp b
WHERE b.deptno = a.deptno
AND b.hiredate > a.hiredate);
--方法2 (ALL)
SELECT *
FROM emp a
WHERE a.hiredate >= ALL
(SELECT b.hiredate FROM emp b WHERE b.deptno = a.deptno);
--方法3 (IN)
SELECT *
FROM emp a
WHERE (a.deptno, a.hiredate) IN
(SELECT b.deptno, MAX(b.hiredate) FROM emp b GROUP BY b.deptno);
--方法4 (IN)
SELECT *
FROM emp a
WHERE (a.deptno, a.hiredate) IN
(SELECT MAX(b.deptno), MAX(b.hiredate)
FROM emp b
WHERE b.deptno = a.deptno);
--方法5 (ROW_NUMBER)
SELECT *
FROM (SELECT a.*,
row_number() over(PARTITION BY a.deptno ORDER BY a.hiredate DESC) rn
FROM emp a)
WHERE rn = 1;
--方法6 (EXISTS)
SELECT *
FROM emp a
WHERE EXISTS
(SELECT 1
FROM emp b
GROUP BY b.deptno
HAVING b.deptno = a.deptno AND MAX(b.hiredate) = a.hiredate);
--方法7 (EXISTS)
SELECT *
FROM emp a
WHERE EXISTS (SELECT 1
FROM emp b
WHERE b.deptno = a.deptno
GROUP BY b.deptno
HAVING MAX(b.hiredate) = a.hiredate);
(
select name,date,col1,rank()over(partition by name order by date desc) mm from t2
)
where mm=1
SELECT *
FROM (SELECT NAME, DATE, col1,
RANK () OVER (PARTITION BY NAME ORDER BY DATE DESC) mm
FROM t2)
WHERE mm = 1
select name,max(date1),max(col1)keep(dense_rank first order by date1 desc) col1
from t1
group by name;
[TEST@orcl] SQL>with t1 as(
2 select 'A' name, '2011/06/07' date1, 4 col1 from dual union all
3 select 'A' name, '2011/06/09' date1, 6 col1 from dual union all
4 select 'A' name, '2011/06/03' date1, 7 col1 from dual union all
5 select 'B' name, '2011/06/29' date1, 5 col1 from dual
6 )select name,max(date1),max(col1)keep(dense_rank first order by date1 desc) col1
7 from t1
8 group by name;N MAX(DATE1) COL1
- ---------- ----------
A 2011/06/09 6
B 2011/06/29 5
select name,max(date) from a group by name;
where B.rn=1