SQL Query, Always show first record of a repeating field

Question

SQL Query, Always show first record of a repeating field

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (1 votes)

1

I'm having trouble listing only one line in each process. The query must always bring the protocols with the last revision. Ex: Show only the process of number 2 of the last revision 2
and Show only the process of number 1 of the last revision 3

Select 
Numero_Processo Processo
, Numero_Revisao Revisao
,Titulo
,Id
,Data_Criacao
 FROM Qualidade_Insp_Inj_Processo 
 WHERE Data_Exclusao is null

order by Numero_Processo desc, Numero_Revisao desc

I've tried Group By, but as the field revision is always different it brings all the protocols with all the revisions

sql query

asked by anonymous 20.06.2018 / 15:35

3 answers

2

Try using it like this:

SELECT
   Numero_Processo Processo
   , Numero_Revisao Revisao
   , Titulo
   , Id
   , Data_Criacao
FROM 
   Qualidade_Insp_Inj_Processo
WHERE
   Id = ( 
      SELECT 
         aux.id 
      FROM
         Qualidade_Insp_Inj_Processo aux 
      WHERE
         aux.Numero_Revisao DESC LIMIT 1 
   ) 
   AND Data_Exclusao IS NULL
ORDER BY 
   Numero_Processo DESC
   , Numero_Revisao DESC

20.06.2018 / 15:39

1

You can filter by Id with sub-select by sorting by revision number:

SELECT 
    p.processo,
    p.revisao,
    p.titulo,
    p.id,
    p.data_criacao
FROM 
    processos p
WHERE 
   p.id = (
      SELECT top 1 x.id 
      FROM processos x 
      WHERE x.processo = p.processo 
      ORDER BY revisao DESC
   );

I put it in SQLFiddle

20.06.2018 / 16:06

PHP - Add everything and give a value Search in lambda query with logical operators

score 1 · Accepted Answer

I was able to use select MAX within the where

SELECT Numero_Processo, Numero_Revisao Revisao,Titulo ,Id ,Data_Criacao 
                                    FROM Qualidade_Insp_Inj_Processo Qproc
                                    WHERE Data_Exclusao is null
                                    AND   Numero_Revisao = (select Max(Numero_Revisao) processo
                                                                from Qualidade_Insp_Inj_Processo
                                                                where Numero_Processo = Qproc.Numero_Processo)