select A,min(B)keep(dense_rank first order by B) as B from table_name group by A;
[SYS@orcl] SQL>with table_name as( 2 select 1 a ,sysdate b from dual union all 3 select 1 a ,sysdate-1 b from dual union all 4 select 1 a ,sysdate-1 b from dual union all 5 select 2 a ,sysdate b from dual union all 6 select 2 a ,sysdate b from dual) 7 select A,min(B)keep(dense_rank first order by B) as B 8 from table_name 9 group by A; A B ---------- ---------- 1 2011-07-13 2 2011-07-14
--方法1 (NOT EXISTS) SELECT * FROM x a WHERE NOT EXISTS (SELECT * FROM x b WHERE b.a = a.a AND b.b > a.b);--方法2 (ALL) SELECT * FROM x a WHERE a.b >= ALL (SELECT b.b FROM x b WHERE b.a = a.a);--方法3 (IN) SELECT * FROM x a WHERE (a.a, a.b) IN (SELECT b.a, MAX(b.b) FROM x b GROUP BY b.a);--方法4 (IN) SELECT * FROM x a WHERE (a.a, a.b) IN (SELECT MAX(b.a), MAX(b.b) FROM x b WHERE b.a = a.a);--方法5 (ROW_NUMBER) SELECT * FROM (SELECT a.*, row_number() over(PARTITION BY a.a ORDER BY a.b DESC) rn FROM x a) WHERE rn = 1;--方法6 (EXISTS) SELECT * FROM x a WHERE EXISTS (SELECT 1 FROM x b GROUP BY b.a HAVING b.a = a.a AND MAX(b.b) = a.b);--方法7 (EXISTS) SELECT * FROM x a WHERE EXISTS (SELECT 1 FROM x b WHERE b.a = a.a GROUP BY b.a HAVING MAX(b.b) = a.b);
with table_name as( select 1 a ,sysdate b from dual union all select 1 a ,sysdate-1 b from dual union all select 1 a ,sysdate-1 b from dual union all select 2 a ,sysdate b from dual union all select 2 a ,sysdate b from dual) select t.a,min(t.b) from table_name t group by t.a
from table_name
group by A;
[SYS@orcl] SQL>with table_name as(
2 select 1 a ,sysdate b from dual union all
3 select 1 a ,sysdate-1 b from dual union all
4 select 1 a ,sysdate-1 b from dual union all
5 select 2 a ,sysdate b from dual union all
6 select 2 a ,sysdate b from dual)
7 select A,min(B)keep(dense_rank first order by B) as B
8 from table_name
9 group by A; A B
---------- ----------
1 2011-07-13
2 2011-07-14
SELECT *
FROM x a
WHERE NOT EXISTS (SELECT *
FROM x b
WHERE b.a = a.a
AND b.b > a.b);--方法2 (ALL)
SELECT * FROM x a WHERE a.b >= ALL (SELECT b.b FROM x b WHERE b.a = a.a);--方法3 (IN)
SELECT *
FROM x a
WHERE (a.a, a.b) IN (SELECT b.a, MAX(b.b) FROM x b GROUP BY b.a);--方法4 (IN)
SELECT *
FROM x a
WHERE (a.a, a.b) IN (SELECT MAX(b.a), MAX(b.b) FROM x b WHERE b.a = a.a);--方法5 (ROW_NUMBER)
SELECT *
FROM (SELECT a.*, row_number() over(PARTITION BY a.a ORDER BY a.b DESC) rn
FROM x a)
WHERE rn = 1;--方法6 (EXISTS)
SELECT *
FROM x a
WHERE EXISTS
(SELECT 1 FROM x b GROUP BY b.a HAVING b.a = a.a AND MAX(b.b) = a.b);--方法7 (EXISTS)
SELECT *
FROM x a
WHERE EXISTS
(SELECT 1 FROM x b WHERE b.a = a.a GROUP BY b.a HAVING MAX(b.b) = a.b);
with table_name as(
select 1 a ,sysdate b from dual union all
select 1 a ,sysdate-1 b from dual union all
select 1 a ,sysdate-1 b from dual union all
select 2 a ,sysdate b from dual union all
select 2 a ,sysdate b from dual) select t.a,min(t.b)
from table_name t
group by t.a
GROUP BY A