By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
5 rows affected
SELECT job_id, modified_date
FROM table_name
GROUP BY job_id, modified_date
HAVING COUNT(*) = 2;
| JOB_ID | MODIFIED_DATE |
|---|---|
| 1 | 2024-10-04 00:00:00 |
SELECT job_id, TRUNC(modified_date)
FROM table_name
GROUP BY job_id, TRUNC(modified_date)
HAVING COUNT(*) = 2;
| JOB_ID | TRUNC(MODIFIED_DATE) |
|---|---|
| 2 | 2024-10-04 00:00:00 |
| 1 | 2024-10-04 00:00:00 |
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;
| 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 |
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;
| 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 |