Get records for same date from table in Oracle

Question

I have a table where I want to get records where MODIFIED_DATE is same with count of 2

For example: If for job_id = 1234 there are 2 records with date MODIFIED_DATE = 04-10-2024 then it should filter that record.

Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER         
HOTO_OFFERED_DATE               DATE           
HOTO_ACCEPTENCE_DATE            DATE           
LIT_ACCEPTENCE_DATE             DATE           
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255)     
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE 

Answer 1

If you want to find the job_id and modified_date when there are two rows in the table with those values then:

SELECT job_id, modified_date
FROM   table_name
GROUP BY job_id, modified_date
HAVING COUNT(*) = 2

Which, for the sample data:

CREATE TABLE table_name (job_id, modified_by, modified_date) AS
SELECT 1, 'Alice', DATE '2024-10-04' FROM DUAL UNION ALL
SELECT 1, 'Betty', DATE '2024-10-04' FROM DUAL UNION ALL
SELECT 2, 'Carol', DATE '2024-10-04' + INTERVAL '01:23:45' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Debra', DATE '2024-10-04' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'Emily', DATE '2024-10-04' FROM DUAL;

Outputs:

JOB_ID	MODIFIED_DATE
1	2024-10-04 00:00:00

If you want to list all the columns then:

SELECT *
FROM   (
  SELECT t.*,
         COUNT(*) OVER (PARTITION BY job_id, modified_date)
           AS num_duplicates_per_job_per_day
  FROM   table_name t
)
WHERE  num_duplicates_per_job_per_day = 2

Outputs:

JOB_ID	MODIFIED_BY	MODIFIED_DATE	NUM_DUPLICATES_PER_JOB_PER_DAY
1	Alice	2024-10-04 00:00:00	2
1	Betty	2024-10-04 00:00:00	2

Note: A DATE always has a date and time component so when you are comparing values then you are comparing with a precision of seconds (not days). If you are storing non-midnight time components and want to check for duplicates across an entire day then use TRUNC(modified_date) rather than modified_date in the queries.

SELECT job_id, TRUNC(modified_date)
FROM   table_name
GROUP BY job_id, TRUNC(modified_date)
HAVING COUNT(*) = 2;

Outputs:

JOB_ID	TRUNC(MODIFIED_DATE)
2	2024-10-04 00:00:00
1	2024-10-04 00:00:00

and:

SELECT *
FROM   (
  SELECT t.*,
         COUNT(*) OVER (PARTITION BY job_id, TRUNC(modified_date))
           AS num_duplicates_per_job_per_day
  FROM   table_name t
)
WHERE  num_duplicates_per_job_per_day = 2;

Outputs:

JOB_ID	MODIFIED_BY	MODIFIED_DATE	NUM_DUPLICATES_PER_JOB_PER_DAY
1	Alice	2024-10-04 00:00:00	2
1	Betty	2024-10-04 00:00:00	2
2	Carol	2024-10-04 01:23:45	2
2	Debra	2024-10-04 23:59:59	2

fiddle