All geek questions in one place

How can I prevent duplicate rows in a selected query?

I was given the task of selecting key data from an Oracle database, but I notice that my selection returns duplicate rows. I do not need them for my report, but I do not want them to delete them. Maybe someone will help to get only the data that I need. I tried the following code, but that does not help.

SELECT distinct bbp.SUBCAR "Treadwell", bbp.BATCH_ID "Batch ID", bcs.SILICON "Si", bcs.SULPHUR "S", bcs.MANGANESE "Mn", bcs.PHOSPHORUS "P", to_char(bcs.SAMPLE_TIME, 'dd-MON-yy hh24:MI') "Sample Time", to_char(bbp.START_POUR, 'dd-MON-yy hh24:MI') "Start Pour Time", to_char(bbp.END_POUR, 'dd-MON-yy hh24:MI') "End pour Time", bofcs.temperature "Temperature" FROM bof_chem_sample bcs, bof_batch_pour bbp, bof_celox_sample bofcs WHERE bcs.SAMPLE_CODE= to_char('D1') AND bbp.BATCH_ID=bcs.BATCH_ID AND bcs.SAMPLE_TIME>=to_date('01-jan-10')
+6
source share
2 answers

If you look at a query translated into SQL Server such as SQL, you will see that there is no connection between your bofcs table and the rest of your data. Basically, it returns every entry in the bofcs temperature field, and this can lead to duplicate results.

 SELECT bbp.SUBCAR "Treadwell", bbp.BATCH_ID "Batch ID", bcs.SILICON "Si", bcs.SULPHUR "S", bcs.MANGANESE "Mn", bcs.PHOSPHORUS "P", to_char(bcs.SAMPLE_TIME,'dd-MON-yy hh24:MI') "Sample Time", to_char(bbp.START_POUR, 'dd-MON-yy hh24:MI') "Start Pour Time", to_char(bbp.END_POUR, 'dd-MON-yy hh24:MI') "End pour Time", bofcs.temperature "Temperature" FROM bof_chem_sample bcs, INNER JOIN bof_batch_pour bbp, ON bbp.BATCH_ID=bcs.BATCH_ID INNER JOIN bof_celox_sample bofcs ON **-- NO RELATION B/N BOFCS and the other tables????** WHERE bcs.SAMPLE_CODE= to_char('D1') AND bcs.SAMPLE_TIME>=to_date('01-jan-10')
+5
source

If the SELECT statement contains DISTINCT, all returned records have a unique combination of values ​​for the selected columns. You need to determine which columns return different values ​​for the records that you think are duplicated.

+6
source

Source: https://habr.com/ru/post/888585/

More articles:


All Articles