Order table values by Day and Month

-1

I have this Select below:

SELECT b.id,
       b.codigo,
       a.codacesso,
       a.seqproduto,
       a.desccompleta,
       b.quantidade,
       b.data_vencimento,
       b.data_atual,
       b.observacao,
       b.usuario,
       b.estado,
       b.loja,
       a.medvdiageral,
       a.comprador,
       a.preco
  FROM master_datas_b a, master_coletores b
 WHERE b.tipo_acao IS NULL
   and a.comprador = :comprador
   and a.nroempresa = :loja
   and b.loja = :loja
   and estado = 'Ativo'
   and b.codigo = a.codacesso
   AND b.data_vencimento BETWEEN TO_DATE(:data1, 'YYYY-MM-DD') AND
       TO_DATE(:data2, 'YYYY-MM-DD')
   and b.quantidade > 0
 ORDER BY b.data_vencimento, b.codigo ASC

It is returning the values but not the way I need it, it is sorting by day, for example:

12/11/2018
13/12/2018
14/11/2018

I need to make the order by day and month and December appear only at the bottom of the list ..

    
asked by anonymous 14.11.2018 / 11:07

2 answers

1

To solve, the easiest way would be to convert the field to date type using to_date() and sort (by problem, your field should be varchar ):

SELECT b.id,
       b.codigo,
       a.codacesso,
       a.seqproduto,
       a.desccompleta,
       b.quantidade,
       b.data_vencimento,
       b.data_atual,
       b.observacao,
       b.usuario,
       b.estado,
       b.loja,
       a.medvdiageral,
       a.comprador,
       a.preco
  FROM master_datas_b a, master_coletores b
 WHERE b.tipo_acao IS NULL
   and a.comprador = :comprador
   and a.nroempresa = :loja
   and b.loja = :loja
   and estado = 'Ativo'
   and b.codigo = a.codacesso
   AND b.data_vencimento BETWEEN TO_DATE(:data1, 'YYYY-MM-DD') AND
       TO_DATE(:data2, 'YYYY-MM-DD')
   and b.quantidade > 0
 ORDER BY TO_DATE(b.data_vencimento, 'YYYY-MM-DD'), b.codigo ASC

detail: Because the field is string , you will certainly have performance loss for queries, since you will have to use conversion functions in where , and this is quite be seen in this question .

    
14.11.2018 / 12:02
1

I discovered the problem, in fact it is a stylization of the bootstrap.

<table class="table table-striped table-bordered bootstrap-datatable datatable">

It references the first column of the table, and the first was the code. I removed the 'datatable'

<table class="table table-striped table-bordered bootstrap-datatable">

And now he's sorting right, so here's the hint: P

    
14.11.2018 / 12:13