现有一表T,里面的JOB_ID有重复的,想提取JOB_ID(不重复)并其中DATE最早的记录,SQL语句该怎么写呢?谢谢呀
JOB_ID DATE
3 2010-10-2
2 2010-10-2
2 2010-10-4
4 2010-10-4
4 2010-11-1
…|
VJOB_ID DATE
3 2010-10-2
2 2010-10-2
4 2010-10-4
…
JOB_ID DATE
3 2010-10-2
2 2010-10-2
2 2010-10-4
4 2010-10-4
4 2010-11-1
…|
VJOB_ID DATE
3 2010-10-2
2 2010-10-2
4 2010-10-4
…
from T
group by job_id
select 3 JOB_ID, to_date('2010-10-2','yyyy-mm-dd') T_DATE from dual union all
select 2, to_date('2010-10-2','yyyy-mm-dd') from dual union all
select 2, to_date('2010-10-4','yyyy-mm-dd') from dual union all
select 4, to_date('2010-10-4','yyyy-mm-dd') from dual union all
select 4, to_date('2010-11-1','yyyy-mm-dd') from dual)
--以上为提供数据的语句
select job_id,min(t_date)
from tb
group by job_id; JOB_ID MIN(T_DATE
---------- ----------
2 2010-10-02
4 2010-10-04
3 2010-10-02
select job_id,min(t_date) from tablename group by job_id;