with res as

(select 사원코드, 출신학교,입학일,졸업일,넘버링

           , row_number() over(partition by 사원코드 order by 입학일 desc ) mno

 from 테이블 )

select 사원코드, 출신학교,입학일,졸업일,넘버링

  from res

where mno = 1

위와 같이 쿼리하면 넘버링=1인 값만 보여집니다.

create table universe

(e_code varchar(10),

 e_uni varchar(15),

 i_date datetime,

 o_date datetime,

 numbering int)

insert into universe values

('홍길동', '한양대학교', '20010301','20050222',1),

('김하수','고려대학교','20020301','20040101',1),

('김하수','고려대석사','20050101','20080101',2),

('이철도','서울고등학교','20080101','20100101',1),

('이철도','서울대학교','20110101','20130101',2),

('김천사','홍익대학교','20010101','20050101',1)

create clustered index c_odate on universe(o_date)

create index uni_code_odate on universe(e_code,o_date)

;with m_uni as

(

select e_code, MAX(o_date) o_date

from universe

group by e_code

)

select m.e_code, u.e_uni, u.i_date,m.o_date,u.numbering 

from m_uni m inner join universe u

on m.o_date=u.o_date