求SQL语句

[c87580506]

表X 有列A,B.......
A是主键，B时间列，比如
a b1
a b2
c d
....

我需要 取出最近时间(b)的信息

即获取到
a b1
c d

求高手帮助

[c87580506]

自己先顶

[c87580506]

b可能重复，重复的取一条即可

[BenChiM888]

select A,min(B)keep(dense_rank first order by B) as B
  from table_name
 group by A;

[BenChiM888]

[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

[tangren]

--方法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);

[horizonlyhw]

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

[c87580506]

谢谢各位大哥啊

[不要悲剧人生]

楼主说的BI,和B2哪个大，是要取最近的时间，那就应该是最大的， 应该用MAX吧， 让人误解用起MIN呢。。

[sh65489]

SELECT A ,MIN(B) FROM TAB
GROUP BY A

[txljerry]

都是高手啊。