MySql Combinate queries

Question

I have a table with the records of different defects in a company. The table is something like this

ITMNBR          Defect                       Reference_Designator   RepairCenter
8800RTO001700   Componente / Placa abierto          U1U2                FG
8800HIB001075V  Componente Equivocado (NumeroParte) R53                 SB
8800HIB001075V  Ensamble Incorrecto (produccion)    R19                 SB
8800RTO000400   Componente / Placa abierto          U1                  SB
8800RTO003200   Componente Polaridad Inversa        ZD2                 SB
8800HIB001048   NO SOLDADURA                        T1                  SB
8800HIB001048   Componente / Placa abierto          U2                  SB
8800HIB001048   Componente / Placa abierto          U2                  SB
Etc.

I want to consult only the three most repetitive defects of manufacture, I made this.

SELECT defect, COUNT(*) FROM reportefallas WHERE RepairCenter ='SB'
AND (CREADT BETWEEN NOW() - INTERVAL 7 DAY AND NOW()) #Select the Dates
AND (Defect IN ('Componente / Placa dañada X alto voltaje','Pin / Patita Quebrado','Componente / Placa Quemada','Componente Defecto Cosmetico','Falla no Duplicada','Soldadura Crackeada','Soldadura Fria','Parametros Incorrectos en la torre','Parametros Incorrectos en el dibujo','Componente dañado fisicamente','Conector mal colocado (inclinado)','Tornillo / Rondana Suelto','Pista Levantada (dañada)','Componente Ausente','Soldadura Derretida','Componente Equivocado (NumeroParte)','NO SOLDADURA','Componente/Placa no programada','Conector mal ensamblado','No se encontro problema','Tornillo / Rondana Flojo','Componente / Placa abierto','Pin Hole','Pin / Pata levantada (no Soldadura)','Componente Polaridad Inversa','Puente de Soldadura','Componente Desfasado Pad','Componente / Placa en corto','Splash de Soldadura','LEDs con VF diferente / equivocado','LEDs con VF alto','LEDs con VF bajo','Ensamble Incorrecto (produccion)','Componente posicion Equivocada (referencia)','Cable ensamblado posicion incorrecta'))
GROUP BY defect
ORDER BY COUNT(*) DES
LIMIT 3;

And I have the next result

Defect                                   COUNT(*)
Componente/ Placa abierto                      5
Componente / Placa daÃ±ada X alto voltaje      4
Componente daÃ±ado fisicamente                 3

Now, I need a query from the same table where the defects are, with only the three most repetitive defects that I already obtained, this is the result that I want:

 ITMNBR         Defect                                          Reference_Designator
    8800ITH001700   Componente / Placa abierto                  F2-U1(SHORT)-U2(SHORT)
    8800ITH001700   Componente / Placa abierto                  F2-U1(SHORT)-U2(SHORT)
    8800ITH001700   Componente / Placa abierto  F2-R29-R22-R19-R32-R13-U1(SHORT)-U2(SHORT)
    8800ITH001700   Componente / Placa abierto  F2-R29-R22-R19-R32-R13-U1(SHORT)-U2(SHORT)
    8800ITH001700   Componente / Placa abierto                  F2
    8850HZL0015EX   Componente / Placa daÃ±ada X alto voltaje   C6-C7
    8800HIB001084   Componente / Placa daÃ±ada X alto voltaje   R7-C20-MOV1
    8850HIB004205   Componente / Placa daÃ±ada X alto voltaje   C21-C42
    8800HIB004220   Componente / Placa daÃ±ada X alto voltaje   R22 SWITH-R44 SWITH
    8850HIB004206   Componente daÃ±ado fisicamente              C42
    8850HIB004202   Componente daÃ±ado fisicamente              F1
    8800HIB0131EX   Componente daÃ±ado fisicamente              R37

I tried the code below, but it doesn’t accept the LIMIT.

SELECT ITMNBR, Defect, Reference_Designator FROM reportefallas
WHERE Defect IN (SELECT defect FROM reportefallas WHERE RepairCenter='SB'
AND(CREADT BETWEEN NOW() - INTERVAL 7 DAY AND NOW()) AND (Defect IN ('Componente / Placa dañada X alto voltaje','Pin / Patita Quebrado','Componente / Placa Quemada','Componente Defecto Cosmetico','Falla no Duplicada','Soldadura Crackeada','Soldadura Fria','Parametros Incorrectos en la torre','Parametros Incorrectos en el dibujo','Componente dañado fisicamente','Conector mal colocado (inclinado)','Tornillo / Rondana Suelto','Pista Levantada (dañada)','Componente Ausente','Soldadura Derretida','Componente Equivocado (NumeroParte)','NO SOLDADURA','Componente/Placa no programada','Conector mal ensamblado','No se encontro problema','Tornillo / Rondana Flojo','Componente / Placa abierto','Pin Hole','Pin / Pata levantada (no Soldadura)','Componente Polaridad Inversa','Puente de Soldadura','Componente Desfasado Pad','Componente / Placa en corto','Splash de Soldadura','LEDs con VF diferente / equivocado','LEDs con VF alto','LEDs con VF bajo','Ensamble Incorrecto (produccion)','Componente posicion Equivocada (referencia)','Cable ensamblado posicion incorrecta'))
GROUP BY defect
ORDER BY COUNT(*) DESC
LIMIT 3)

Does anyone have any ideas any ideas?

Sorry for the Spanglish and the bad English, I hope you can understand.

possible duplicate of [MySQL Subquery LIMIT](http://stackoverflow.com/questions/2856397/mysql-subquery-limit) — Air, Apr 02 '14 at 18:55

score 1 · Accepted Answer · answered Apr 02 '14 at 19:04

There are several options. Previous questions on this topic have suggested using JOIN to trim down your result set instead of IN, which would look something like this:

SELECT rf.ITMNBR, rf.Defect, rf.Reference_Designator 
FROM (SELECT defect FROM reportefallas WHERE RepairCenter='SB'
    AND(CREADT BETWEEN NOW() - INTERVAL 7 DAY AND NOW()) AND (Defect IN ('Componente / Placa dañada X alto voltaje','Pin / Patita Quebrado','Componente / Placa Quemada','Componente Defecto Cosmetico','Falla no Duplicada','Soldadura Crackeada','Soldadura Fria','Parametros Incorrectos en la torre','Parametros Incorrectos en el dibujo','Componente dañado fisicamente','Conector mal colocado (inclinado)','Tornillo / Rondana Suelto','Pista Levantada (dañada)','Componente Ausente','Soldadura Derretida','Componente Equivocado (NumeroParte)','NO SOLDADURA','Componente/Placa no programada','Conector mal ensamblado','No se encontro problema','Tornillo / Rondana Flojo','Componente / Placa abierto','Pin Hole','Pin / Pata levantada (no Soldadura)','Componente Polaridad Inversa','Puente de Soldadura','Componente Desfasado Pad','Componente / Placa en corto','Splash de Soldadura','LEDs con VF diferente / equivocado','LEDs con VF alto','LEDs con VF bajo','Ensamble Incorrecto (produccion)','Componente posicion Equivocada (referencia)','Cable ensamblado posicion incorrecta'))
    GROUP BY defect
    ORDER BY COUNT(*) DESC #Ordena de manera descendente
    LIMIT 3) AS subquery
JOIN
    reportefallas AS rf USING (Defect)

Alternatively, you could create a separate table to track the three most common defects, and periodically update that table (e.g. via a cron job). Then you would SELECT ... WHERE Defect IN this other table.

Either of these methods could provide better performance, depending on the situation. If you try one and have poor performance, try the other and see if it's an improvement.

(For that matter, you could also store that enormous list of defects in another table, to make your query cleaner.)

John Ruddell · Answer 2 · 2014-04-02T21:40:29.343

-1

just like AirThomas said you can use a subquery.. you should also be able to do a simple select inside your IN() instead of listing out each one individually. this is another way to do the subquery though

SELECT rf.ITMNBR, rf.Defect, rf.Reference_Designator 
FROM(
    SELECT ITMNBR as itm_number, defect, COUNT(*) as top_three FROM reportefallas WHERE RepairCenter ='SB'
    AND (CREADT BETWEEN NOW() - INTERVAL 7 DAY AND NOW()) -- Select the Dates
    AND (Defect IN (SELECT defect from reportefallas))
    GROUP BY defect
    ORDER BY top_three DES
    LIMIT 3
)as t
JOIN reportefallas rf ON rf.ITMNBR = t.itm_number

edited Apr 02 '14 at 21:40

answered Apr 02 '14 at 19:16

John Ruddell

25,283
6
57
86

`SELECT ... FROM reportefallas WHERE ... Defect IN (SELECT defect from reportefallas)` is completely redundant. The implication of having a list of defects to select is that the list does not comprise all possible values of `Defect` in the table. – Air Apr 02 '14 at 21:17
if the OP has a list of defects in his table, instead of writing them all out it seems like it would be simpler to filter off of them when selected. don't see how its redundant since hes writing them all out already – John Ruddell Apr 02 '14 at 21:30
The only time any row would be filtered by such a condition would be when `Default` is `NULL`, in which case `WHERE Default IS NOT NULL` would be the obvious condition to use rather than a subquery. What you're saying is, give me every row from the table that has a value that exists (in the same table); and barring `NULL` ( which is not a value) that is a tautology. (e.g. http://sqlfiddle.com/#!2/6cd03/1) – Air Apr 02 '14 at 21:52
I know what it is doing... from the list that the OP provided it looks like it is every value in the table.. `I want to consult only the three most repetitive defects of manufacture` he wants to count ALL of the defects and return the 3 most repetitive ones. this gets all of the defects. i think the OP should run it off of his database and let me know if its wrong, because I don't know why this would'nt work – John Ruddell Apr 03 '14 at 01:08
I have a list of Defects, but not all the defects are from Manufacture, and also I just want the Top3 of these defects. – Disc Apr 03 '14 at 15:25
so there are additional ones outside of this table? or less than the ones in this table – John Ruddell Apr 03 '14 at 15:28
That is just a sample of the table. I have a lot of defects in the table, a few of them are from supplier. – Disc Apr 03 '14 at 15:33
ok so my last question is... the list of defects you have... is that all of the defects? – John Ruddell Apr 03 '14 at 18:16

MySql Combinate queries

2 Answers2