Sort table values by Day and Month

Asked

Viewed 51 times

-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

He’s returning the values but not the way I need, he’s ordering by day, for example:

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

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

sql oracle
  • 1

    According to his example the ordination is correct. ASC ordination is increasing, so the date 13/12/2018 really comes after the 12/11/2018.

  • problem is that it is so 12/11/2018 - 13/12/2018 - 14/11/2018

  • I edited your question to add the example of the way you put in the comment.

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 type string, you will definitely lose performance for queries, since you will need to use conversion functions in the where, and this compromises a lot, as can be seen in this question.

1

I figured out the problem, it’s actually a bootstrap stylization.

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

he reference by 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 it’s ordering correctly, it’s tip :P

Browser other questions tagged sql oracle

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