row_number() oracle
--DROP TABLE BUILDING_MANAGER;
CREATE TABLE BUILDING_MANAGER
(BUILDING_NO1 VARCHAR2(3) NOT NULL ENABLE,
BUILDING_NO2 VARCHAR2(5) NOT NULL ENABLE,
MANAGER_NAME VARCHAR2(30) NOT NULL ENABLE,
SAFE_APPOINT_DE VARCHAR2(8) NOT NULL ENABLE,
SAFE_LICENSE_NO VARCHAR2(30),
REGIST_DT DATE NOT NULL ENABLE,
CONSTRAINT PK_BULDING_MANAGER PRIMARY KEY (BUILDING_NO1, BUILDING_NO2, SAFE_LICENSE_NO)
);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','200','lisa','20150923','00100123F',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','875','tyra','20150722','00109921I',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('315','123','emma','20140123','00178542K',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('211','229','sally','20120722','00153211C',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('211','229','hera','20110329','00198723B',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','875','sophia','20130730','00158322A',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('315','123','wendy','20120211','00122231E',sysdate);
COMMIT;
SELECT * FROM BUILDING_MANAGER;
----------------
result:
=================================================================================================
SELECT ROW_NUMBER() OVER(PARTITION BY BUILDING_NO1, BUILDING_NO2 ORDER BY SAFE_APPOINT_DE) RN
,BUILDING_NO1
,BUILDING_NO2
,MANAGER_NAME
,SAFE_APPOINT_DE
,SAFE_LICENSE_NO
FROM BUILDING_MANAGER
--------
result :
=================================================================================================
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY BUILDING_NO1, BUILDING_NO2 ORDER BY SAFE_APPOINT_DE) RN
,BUILDING_NO1
,BUILDING_NO2
,MANAGER_NAME
,SAFE_APPOINT_DE
,SAFE_LICENSE_NO
FROM BUILDING_MANAGER)
WHERE RN = 1
---------------------------------------------------------------------------------------------------
result :
=================================================================================================
ROW_NUMBER() and RANK()
SELECT RANK() OVER(PARTITION BY BUILDING_NO1, BUILDING_NO2 ORDER BY REGIST_DT) RN
,BUILDING_NO1
,BUILDING_NO2
,MANAGER_NAME
,SAFE_APPOINT_DE
,SAFE_LICENSE_NO
,REGIST_DT
FROM BUILDING_MANAGER
UNION ALL
SELECT row_number() OVER(PARTITION BY BUILDING_NO1, BUILDING_NO2 ORDER BY REGIST_DT) RN
,BUILDING_NO1
,BUILDING_NO2
,MANAGER_NAME
,SAFE_APPOINT_DE
,SAFE_LICENSE_NO
,REGIST_DT
FROM BUILDING_MANAGER
-------
result:
