Return last contributor who checked the car

Question

I intend to return the last contributor who checked the car products before what you are currently checking.

The car has to be the same and just in case there’s any product checked in the car like Não OK.

Query:

SELECT nome, DATE(C.DataRegisto4) AS Data, CarrosAla.Descricao, 
ListagemPrduto.Descricao, C.Sim, C.DataP, C.Stock, C.Obsevacao3,
(SELECT Colaborador FROM RegistoListagemCarros AS A LEFT OUTER JOIN usuarios AS B
ON B.id = A.Colaborador WHERE A.IdCarro = C.IdCarro ORDER BY A.Id DESC LIMIT 1,1) AS `Colaborador Anterior`
WHERE (C.Sim = 'Não Ok' OR C.DataP = 'Não Ok')  AND Colaborador = '33'
AND DATE(C.DataRegisto4) = '2019-08-12'

The problem is in this part of the query:

(SELECT Colaborador FROM RegistoListagemCarros AS A LEFT OUTER JOIN usuarios AS B
ON B.id = A.Colaborador WHERE A.IdCarro = C.IdCarro ORDER BY A.Id DESC LIMIT 1,1) AS `Colaborador Anterior`

Here I intend to return the last collaborator who checked the car, but at this time returns the collaborator who is checking at the moment.

I leave the example with the tables with which I am creating the query:

Example

In the column Colaborador Anterior should return the collaborator 27, but is returning the 33, which was the last who checked the car.

Answer 1

From the MySQL 8.0 you can follow the following steps:

• Add an incremental column to link events according to the history. To do this use the function ROW_NUMBER linking the columns IdCarro and IdProduto and sorting through the column DataRegisto4 in undefined order facilitating the identification of the last record in the history, which will possess the order 1:

  ROW_NUMBER() OVER(PARTITION BY rlc.IdCarro, rlcIdProduto ORDER BY rlc.DataRegisto4 DESC) AS ordem
  

• Use the clause WITH to store the previous information, facilitating the link with the records of other checks:

  WITH historico AS (
    SELECT rlc.*,
           ROW_NUMBER() OVER(PARTITION BY rlc.IdCarro, IdProduto ORDER BY rlc.DataRegisto4 DESC) AS ordem
      FROM RegistoListagemCarros rlc
  )
  

• Make the connection with a LEFT JOIN (to ensure that, if there is no previous record, the event will still be shown) in the CTE historico linking by the columns IdCarro and IdProduto and with the ordem + 1 ensuring that it will be the next record of the history that, according to what was explained earlier, will have the next order:

  LEFT JOIN historico h2
    ON h2.IdCarro = h.IdCarro
   AND h2.IdProduto = h.IdProduto
   AND h2.ordem = h.ordem + 1
  

• In the WHERE the filter shall be made by ordem = 1 ensuring that the last event will be verified:

  WHERE h.ordem = 1
  

The full query would look like the following:

WITH historico AS (
  SELECT rlc.*,
         ROW_NUMBER() OVER(PARTITION BY rlc.IdCarro, rlc.IdProduto ORDER BY rlc.DataRegisto4 DESC) AS ordem
    FROM RegistoListagemCarros rlc
)
SELECT u.nome,
       h.DataRegisto4 AS data,
       ca.Descricao1,
       lp.Descricao,
       h.Sim,
       h.DataP,
       h.Stock,
       h.Obsevacao3,
       h2.id AS idAnterior,
       u2.nome AS colaboradorAnterior
  FROM historico h
  LEFT JOIN historico h2
    ON h2.IdCarro = h.IdCarro
   AND h2.IdProduto = h.IdProduto
   AND h2.ordem = h.ordem + 1
  LEFT JOIN usuarios u2
    ON u2.id = h2.Colaborador
 INNER JOIN ListagemPrduto lp
    ON lp.Id = h.IdProduto
 INNER JOIN CarrosAla ca
    ON ca.Id = h.IdCarro
 INNER JOIN usuarios u
    ON u.id = h.Colaborador
 WHERE h.ordem = 1
   AND (h.Sim = 'Não Ok' OR h.DataP = 'Não Ok');

Which results in:

| nome   | data                | Sim    | DataP | Stock | Obsevacao3 | Descricao1 | Descricao | idAnterior | colaboradorAnterior |
| ------ | ------------------- | ------ | ----- | ----- | ---------- | ---------- | --------- | ---------- | ------------------- |
| Teste1 | 2019-08-13 14:58:45 | Não OK | OK    | OK    | Teste      | G3 Ala A   | Teste11   | 323        | Teste               |
| Teste1 | 2019-08-13 14:58:45 | Não OK | OK    | OK    | Teste      | G3 Ala A   | Teste16   | 327        | Teste               |
| Teste1 | 2019-08-13 14:58:45 | Não OK | OK    | OK    | Teste      | G3 Ala A   | Teste21   | 332        | Teste               |

---

You can see the query working in the DB Fiddle.

---

ROW_NUMBER

Numbers the output of a set of results. More specifically, it returns the sequential number of a row in a partition of a result set, starting at 1 on the first row of each partition.

Answer 2

I rode a query however, it may be necessary to do some validations to see if it works in all scenarios.

It was not clear to me one thing, if collaborator 33 check the car 3 times, theoretically he was the last to check, however I added two options in the query and you can decide what you need for business.

SELECT 
    nome, 
    DATE(C.DataRegisto4) AS Data, 
    Descricao1, 
    Descricao, 
    C.Sim, 
    C.DataP, 
    C.Stock, 
    C.Obsevacao3,
    (SELECT Colaborador FROM RegistoListagemCarros AS A LEFT OUTER JOIN usuarios AS B ON B.id = A.Colaborador 
            WHERE A.ID <> C.Id AND Colaborador <> '33' order by A.DataRegisto4 desc LIMIT 1,1) AS `Colaborador Anterior`,
    (SELECT A.ID FROM RegistoListagemCarros AS A LEFT OUTER JOIN usuarios AS B ON B.id = A.Colaborador 
            WHERE A.ID <> C.Id AND Colaborador <> '33' order by A.DataRegisto4 desc LIMIT 1,1) AS `ID Item Anterior`  ,     

    (SELECT Colaborador FROM RegistoListagemCarros AS A LEFT OUTER JOIN usuarios AS B ON B.id = A.Colaborador 
            WHERE A.ID <> C.Id  order by A.DataRegisto4 desc LIMIT 1,1) AS `Colaborador Anterior 2`,
    (SELECT A.ID FROM RegistoListagemCarros AS A LEFT OUTER JOIN usuarios AS B ON B.id = A.Colaborador 
            WHERE A.ID <> C.Id order by A.DataRegisto4 desc LIMIT 1,1) AS `ID Item Anterior 2`               
FROM RegistoListagemCarros AS C 
    LEFT OUTER JOIN ListagemPrduto  ON ListagemPrduto.Id    = C.IdProduto
    LEFT OUTER JOIN CarrosAla       ON CarrosAla.Id         = C.IdCarro
    LEFT OUTER JOIN usuarios        ON usuarios.id          = C.Colaborador
WHERE 1=1
    AND (C.Sim = 'Não Ok' OR C.DataP = 'Não Ok') 
    AND Colaborador = '33' 
    AND DATE(C.DataRegisto4) = '2019-08-13' 

What has been done?

Selected data sorting by date and on Where the current item has been removed. If you want to check the last contributor(not considering the current one), it takes the last record that is not from the contributor

Answer 3

Bruno adds the Product Id, like this:

ON B.id = A.Colaborador WHERE A.IdCarro = C.IdCarro AND A.IdProduto = C.IdProduto ORDER BY A.Id DESC LIMIT 1,1)