Query bringing all records, and filter the repeated

Asked

Viewed 105 times

3

Have table mov_pallet(some records):

movpallet  rua altura   posicao hora        produto     data        pallet
1.424.959   1   C       4       09:30:07    18.342      02.05.2016  456.074            
1.424.960   1   C       4       09:27:07    18.399      05.05.2016  456.182 
1.392.949   1   A       3       13:25:25    71.367      21.03.2016  426.907 
1.388.727   1   A       4       11:41:08    71.711      15.03.2016  423.018 
1.413.547   1   B       2       16:18:41    29.245      19.04.2016  445.891 
1.413.459   1   B       3       14:04:45    29.245      20.04.2016  445.816 
1.411.489   1   C       3       10:11:45    18.399      18.04.2016  443.976 
1.411.600   2   C       3       13:04:41    18.399      18.04.2016  444.081 
1.414.293   3   D       2       11:32:00    29.244      20.04.2016  446.559 
1.414.294   3   D       2       11:32:06    29.244      19.04.2016  446.559 
1.414.285   3   D       3       11:23:24    29.244      21.04.2016  446.552 
1.226.594   6   B       4       14:13:51    1.016       28.07.2015  278.085 
1.424.843   6   B       4       06:58:39    722.841     04.05.2016  456.076 
1.422.912   6   C       2       08:27:17    59.256      03.05.2016  454.337

I want her to return me like this:

movpallet  rua altura   posicao hora        produto     data        pallet
1.424.959   1   C       4       09:30:07    18.342      02.05.2016  456.074 (eliminar esse)             
1.424.960   1   C       4       09:27:07    18.399      05.05.2016  456.182 
1.392.949   1   A       3       13:25:25    71.367      21.03.2016  426.907 
1.388.727   1   A       4       11:41:08    71.711      15.03.2016  423.018 
1.413.547   1   B       2       16:18:41    29.245      19.04.2016  445.891 
1.413.459   1   B       3       14:04:45    29.245      20.04.2016  445.816 
1.411.489   1   C       3       10:11:45    18.399      18.04.2016  443.976 
1.411.600   2   C       3       13:04:41    18.399      18.04.2016  444.081 
1.414.293   3   D       2       11:32:00    29.244      20.04.2016  446.559 (esse é igual o proximo mas ele continua, pois o produto e pallet sao iguais)
1.414.294   3   D       2       11:32:06    29.244      19.04.2016  446.559 
1.414.285   3   D       3       11:23:24    29.244      21.04.2016  446.552 
1.226.594   6   B       4       14:13:51    1.016       28.07.2015  278.085 (eliminar esse)
1.424.843   6   B       4       06:58:39    722.841     04.05.2016  456.076 
1.422.912   6   C       2       08:27:17    59.256      03.05.2016  454.337

Note: I have N records in this table.

mysql sql select-sql

  • what would be repeated record? with equal street, height and position?

  • The two records you marked to delete have nothing in common to consider repeated. Are there other associated tables? If it is as Rodrigo mentioned (street + height + position) confirm us.

  • @Rodrigodebonasartor that same to (street height and position), are equal.

  • @In fact the records I have marked have no relation between them, but they relate to the next record of him (repeating the street, height, position). for example the record #1,424,959 relates to the 1,424,960 where (repeats street.height, and position)

  • and 1,226,594 has a relationship with 1,424,843 ?

2 answers

1

select a.*
  from mov_pallet a
  left join mov_pallet b 
    on( b.movpallet > a.movpallet
    and b.rua = a.rua
    and b.altura = a.altura
    and b.posicao = a.posicao
    and (b.produto != a.produto or b.pallet != a.pallet) )
where b.movpallet is null

0

Friend, if I understand your question correctly, try something like this:

SELECT *
  FROM mov_pallet mp
 INNER JOIN (SELECT MAX(movpallet) AS movpallet
               FROM mov_pallet
              GROUP BY rua, altura, posicao) mmp ON mp.movpallet = mmp.movpallet

  • It worked in parts. But the record 1,414,293 would have to continue.

  • I had answered before your editing. If you get a solution I put it for you here.

Browser other questions tagged mysql sql select-sql

You are not signed in. Login or sign up in order to post.