SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get minimum value using PARTITION BY multiple columns

rthampiSep 29 2021 — edited Sep 29 2021

Hello
I have a table with the following structure

CREATE TABLE "TENTER" 
  ("EMPLOYEE_NUMBER" VARCHAR2(30 BYTE), 
   "PUNCH_TYPE" CHAR(1 BYTE), 
   "PUNCH_TIME" DATE, 
   "MACHINE_NAME" VARCHAR2(30 BYTE), 
   "CREATED_BY" NUMBER, 
   "CREATION_DATE" DATE DEFAULT sysdate, 
   "LAST_UPDATED_BY" NUMBER, 
   "LAST_UPDATE_DATE" DATE
  )

and sample data as below

REM INSERTING into TENTER
SET DEFINE OFF;
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('01-SEP-21 07:58:37','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('02-SEP-21 07:50:51','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('03-SEP-21 11:11:47','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('05-SEP-21 08:31:33','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('05-SEP-21 17:04:42','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('06-SEP-21 08:16:31','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('06-SEP-21 17:01:27','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('07-SEP-21 08:21:21','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('08-SEP-21 07:21:55','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('08-SEP-21 16:58:03','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('08-SEP-21 07:54:32','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('09-SEP-21 07:46:55','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('09-SEP-21 17:16:45','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('12-SEP-21 07:40:29','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('13-SEP-21 07:40:44','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('14-SEP-21 07:41:42','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('15-SEP-21 07:42:12','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('16-SEP-21 07:50:13','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('16-SEP-21 17:06:49','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('19-SEP-21 17:04:26','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('19-SEP-21 07:39:17','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('20-SEP-21 17:12:26','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('20-SEP-21 07:42:00','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('20-SEP-21 17:12:38','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('21-SEP-21 07:46:23','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('21-SEP-21 16:55:37','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('22-SEP-21 07:52:12','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('22-SEP-21 16:56:33','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('23-SEP-21 08:29:30','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('23-SEP-21 17:03:27','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('26-SEP-21 08:05:32','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('26-SEP-21 17:02:49','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('27-SEP-21 07:45:05','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('27-SEP-21 16:56:54','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('28-SEP-21 07:39:40','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('28-SEP-21 07:40:00','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','1',to_date('28-SEP-21 17:03:52','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);
Insert into TENTER (EMPLOYEE_NUMBER,PUNCH_TYPE,PUNCH_TIME,MACHINE_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) values ('0046','0',to_date('29-SEP-21 07:50:05','DD-MON-RR HH24:MI:SS'),'Head Office Sabhan',null,null,null,null);

Now I want to get the minimum punch time for each day using PARTITION BY and without using GROUP clause as the Machine Name could be different for the each individual type of punching time.
So far I have reached to this:

SELECT employee_number,PUNCH_TYPE IN_TYPE,MACHINE_NAME IN_MACHINE, TRUNC(PUNCH_TIME) PUNCH_DATE,
MIN(PUNCH_TIME) OVER (PARTITION BY TRUNC(PUNCH_TIME), EMPLOYEE_NUMBER) AS PUNCH_IN 
FROM TENTER
WHERE PUNCH_TYPE = '0' and EMPLOYEE_NUMBER = '0046' AND TO_CHAR(PUNCH_TIME,'MMYYYY') = '092021';

The problem is, for the date 8th September, as I have punched twice (PUNCH_TYPE "0") both the rows are picked in the output. I only need the lowest punch time and the associated the finger print machine name. How I could achieve this?

Regards,

This post has been answered by mathguy on Sep 29 2021

Jump to Answer

Added on Sep 29 2021

9 comments

7,858 views

3 mentions