2

I try to delete unnecessary time because I just need min() and max() data, from selected id. And also read same problem through Stack Overflow:

But many command using select. Because delete except min() and max() data by id.

Here is my query:

DELETE FROM table1
WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- List all the IDs you want to filter here
  AND jam NOT IN (SELECT MIN(jam) 
                  FROM table1 
                  WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- Repeat the list of IDs here
                  UNION
                  SELECT MAX(jam) 
                  FROM table1 
                  WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- Repeat the list of IDs here
                 );

SELECT 
    table1.ID_karyawan, table1.nama_karyawan, 
    table1.jam, table1.tanggal, table1.arah
FROM 
    table1 
GROUP BY 
    ID_karyawan, nama_karyawan, jam, tanggal, arah

This is my table (table1) :

ID karyawan nama karyawan   jam          tanggal    arah
-------------------------------------------------------------
1   ridho azhar megantara   07:44:45    2023-07-20  masuk
1   ridho azhar megantara   17:04:46    2023-07-20  keluar
3   Hendy Arief Yuwono  17:24:47    2023-07-20  keluar
3   Hendy Arief Yuwono  06:58:41    2023-07-20  masuk
3   Hendy Arief Yuwono  17:24:41    2023-07-20  keluar
4   Ety wulandari           07:51:48    2023-07-20  masuk
4   Ety wulandari           17:04:07    2023-07-20  keluar
5   Joseph Tan          17:03:48    2023-07-20  keluar
5   Joseph Tan          07:40:31    2023-07-20  masuk
6   Herry Joko Susilo   17:04:16    2023-07-20  keluar
6   Herry Joko Susilo   07:26:11    2023-07-20  masuk
6   Herry Joko Susilo   07:26:16    2023-07-20  masuk
7   Martha Ayu Wulandari    07:49:53    2023-07-20  masuk
7   Martha Ayu Wulandari    07:50:23    2023-07-20  masuk
7   Martha Ayu Wulandari    17:04:43    2023-07-20  keluar

into this:

ID karyawan nama karyawan   jam          tanggal    arah
-----------------------------------------------------------
1   ridho azhar megantara   07:44:45    2023-07-20  masuk
1   ridho azhar megantara   17:04:46    2023-07-20  keluar
3   Hendy Arief Yuwono  06:58:41    2023-07-20  masuk
3   Hendy Arief Yuwono  17:24:41    2023-07-20  keluar
4   Ety wulandari           07:51:48    2023-07-20  masuk
4   Ety wulandari           17:04:07    2023-07-20  keluar
5   Joseph Tan          17:03:48    2023-07-20  keluar
5   Joseph Tan          07:40:31    2023-07-20  masuk
6   Herry Joko Susilo   07:26:11    2023-07-20  masuk
6   Herry Joko Susilo   17:04:16    2023-07-20  keluar
7   Martha Ayu Wulandari    07:49:53    2023-07-20  masuk
7   Martha Ayu Wulandari    17:04:43    2023-07-20  keluar

But the fact the all selected id will delete permanently if execute the query for more than 1.

Like this:

3   Hendy Arief Yuwono  06:58:41    2023-07-20 00:00:00.000
3   Hendy Arief Yuwono  17:24:47    2023-07-20 00:00:00.000
8   Aries Krisnawan         07:49:06    2023-07-20 00:00:00.000
8   Aries Krisnawan         07:49:11    2023-07-20 00:00:00.000
8   Aries Krisnawan         17:04:30    2023-07-20 00:00:00.000

So all selected id disappear. Is my query wrong? Or do you have any suggestion for this?

unlimited system
  • 45
  • 6

1 Answers1

2

You can first select both min and max date per id in a CTE and then delete all other rows. That's the basic idea:

WITH dates AS
(SELECT 
  id, MIN(yourdate) AS minDate, 
  MAX(yourdate) AS maxDate
 FROM table1
 GROUP BY id)
DELETE FROM table1
  WHERE 
    NOT EXISTS 
      (SELECT 1 FROM dates d 
        WHERE table1.id = d.id 
          AND table1.yourdate IN (d.minDate, d.maxDate));

Here a fiddle example that shows this command will not accidentally delete any further rows when executed more than once.

It's unclear to me if you need to add further conditions, GROUP BY or anything else. You should be able to understand the idea and make necessary changes for your use case if required.

A last, but important note: In my answer, I take the min and max date, not the time. That's not a mistake, but I think saving date and time in separate columns is a very bad idea. I don't know if you really do that or if you just formatted your sample data incorrectly. Always use one single column and store the whole date with time there. This makes it much easier to determine earliest or latest date(time), as for example needed here.

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17