Query in SQL query with Group by and Order by

Question

Query in SQL query with Group by and Order by

Navigation

#1 by (0 votes)

0

Good afternoon, everyone!

I have a sql query that is simple but I'm having a hard time performing an order by two fields at the same time. Such a query is for me to assemble a report, but before assembling I need to validate this query. The query that is being performed is this:

select 
   cped.razsocial,
   cped.nrpedido,
   iped.codbarra,
   iped.codprod,
   iped.qtde,
   iped.descricao,
   funcionario.fantasia,
   cped.dtpedido,
   produto.observacao,
   cped.liberado,
   cped.statusweb
from cped

inner join iped on (cped.nrpedido = iped.nrpedido)
inner join produto on (iped.codprod = produto.codprod)
inner join funcionario on (cped.indicacao = funcionario.funcionario)
inner join almox on (cped.almoxarifado = almox.codigo )

where (
  (cped.tipo = 'Z') )


group by 
  cped.razsocial,
  cped.nrpedido,
  iped.codbarra,
  iped.codprod,
  iped.qtde,
  iped.descricao,
  funcionario.fantasia,
  cped.dtpedido,
  produto.observacao,
  cped.liberado,
  cped.statusweb

order by iped.descricao,cped.nrpedido

When executing the command it works fine, however it only orders by iped.description. I need it to order by description but the field must be attached, ie if the customer has 3 items within the order with descriptions of different items, he does not throw the order at the end of the list but leave it grouped. / p>

Thank you in advance.

sql firebird

asked by anonymous 13.05.2018 / 19:27

1 answer

MongoDB Extension for PHP 7.2.3 on Ubuntu 18.04 Adding Select value, however Adding new Selects with (Java Script)

score 0 · Answer 1

TL

If I correctly inferred your model, to get the desired result your order by should look like this:

order by cped.razsocial, cped.nrpedido, iped.descricao

Explaining

You do not sort by two fields at a time . You sort by one field and then another.

I think you've confused the concept of ordering a bit. You see, assuming the following set of data:

joão  | banana  | 1
joão  | maçã    | 1
maria | banana  | 2
maria | banana  | 3
maria | maça    | 3
maria | banana  | 4
joão  | maçã    | 5
joão  | acerola | 5
maria | acerola | 6

According to your query , the sort result for this dataset would look like this:

joão  | acerola | 5
maria | acerola | 6
joão  | banana  | 1
maria | banana  | 2
maria | banana  | 3
maria | banana  | 4
joão  | maçã    | 1
maria | maça    | 3
joão  | maçã    | 5

That is, it is sorted by the item description and then by order number . First, all the acerolas will appear with your request and then the bananas will appear and then list the apples. Regardless of the order in which these items were ordered.

For your text, your expectation would be this result:

joão  | banana  | 1
joão  | maçã    | 1
joão  | maçã    | 5
joão  | acerola | 5
maria | banana  | 2
maria | banana  | 3
maria | maça    | 3
maria | banana  | 4
maria | acerola | 6

In other words: customer , then request and then by item description .

I hope I have helped.