How to add up the amount of records in which the penultimate record enters the Where?

Question

I need a query, in MYSQL, that checks the penultimate record and that this record is equal to a certain value, and that counts the amount of records found.

Ex: penultimate record is John’s. I need to count the number of times John appears as penultimate record.

I tried this way, putting a group by, but I was unsuccessful:

SELECT COUNT(*) FROM chamado AS chmd
INNER JOIN complemento AS comp ON (comp.num_chamado = chmd.num_chamado)
WHERE comp.nome like 'joao%'
GROUP BY comp.num_chamado
ORDER BY comp.id_complemento DESC LIMIT 1,1

Would anyone have any suggestions?

Answer 1

You can get the desired result by calculating the position of the record JOÃO on the daughter table.

SELECT COUNT(1)
  FROM chamado chmd
       INNER JOIN (SELECT @row_number := CASE
                                           WHEN @num_chamado = comp.num_chamado THEN @row_number + 1
                                           ELSE 1
                                         END AS row_number,
                          @num_chamado := comp.num_chamado as num_chamado,
                          comp.id_complemento,
                          comp.nome
                     FROM complemento comp
                    ORDER BY comp.num_chamado, comp.id_complemento DESC) x ON x.num_chamado = chmd.num_chamado
 WHERE x.nome LIKE 'JOÃO%'
   AND x.row_number = 2

---

• Performed a JOIN with a subquery which must return the complement records with their respective position;

• We use the variable @row_number that will be restarted (with value 1) whenever the current call number (comp.num_chamado) is different from the previous call @num_chamado. Otherwise the variable will be incremented;

• We assign the number of the current line call to the variable;

• The ORDER BY is performed first by the call number, ensuring that there are no returns to the same code if it has already passed to the next one. Soon after it is performed the ordering by the code of the decreasing complement (DESC) ensuring that the last code gets row_number 1 and the penultimate 2;

• With the result we filter with the LIKE the names that begin with JOÃO and the records they hold row_number equal to 2, that is, are the second record considering the id_complemento back to front;

• We used the COUNT to verify the amount of records that meet these conditions.

---

See working on SQL Fiddle.

Reference: Mysql row_number, This Is How You Emulate It

Answer 2

Without the structure of the tables it gets a little complicated, but try so:

SELECT 
COUNT(distinct chmd.num_chamado) 
FROM chamado AS chmd
INNER JOIN complemento AS comp ON comp.num_chamado = chmd.num_chamado 
                               AND comp.id_complemento = 
                                 (select 
                                      max(x.id_complemento) 
                                  from complemento x 
                                  where x.num_chamado = chmd.num_chamado 
                                  and x.id_complemento < 
                                     (select 
                                          max(y.id_complemento) 
                                      from complemento y 
                                      where y.num_chamado = x.num_chamado))
WHERE comp.nome like 'joao%'