how can I use group by next to order by

Asked

Viewed 3,740 times

1

how to use the GROUP BY along with the ORDER BY? need to order my tabela so ASC but I can’t use GROUP BY how should I use the two together?

code snippet:

mysqli_query($conn, "SELECT nome_fotos, img, id_cliente FROM alboom WHERE id_fot = '$getid' GROUP BY id_cliente ORDER BY id ASC");

this and my code but it does not order the photos.

the result after the Workbench consultation was

nome_fotos = merry
img = merry.jpg
id_cliente = 2

that is it has not ordered because I have id = 1 in the database.

php mysql mysqli

  • Which error appears?

  • not error it just does not show the image in the order I want

  • When you group information, you are hiding everything that repeats, however, you will not be able to "see" other information from these grouped records, i.e., you can no longer have fields in the select that in his group by. It won’t work. A order by also makes no sense inside an undercurrent. If you do not want to show the repeats, use the distinct and to order use the correct field. Check that your WHERE is not limiting to only one (1) record your query not differentiating in ASC and DESC.

  • What is the point of using the GROUP BY clause without an aggregation function? I don’t know if it was just a typo but you sort by this id field that doesn’t appear anywhere else in the SELECT command.

  • why use group by if the id_client does not repeat itself?

2 answers

1

In charge select you inform all the columns you are requesting in your query. What is not on select is not part of the consultation.

If in command select you did not request the field id then you cannot use it later in the command order by.

That’s why your query doesn’t work... you’re requesting a order by in a column that does not exist in the query result.

0

Untested, but I believe you can separate the ordination into a subselect:

SELECT * FROM (
  SELECT nome_fotos, img, id_cliente, id_fot FROM alboom 
         WHERE id_fot = '$getid' ORDER BY id_cliente ASC
) AS tabela GROUP BY id_cliente

  • did not work continued in the same

  • tested like this: mysqli_query($conn,"SELECT * FROM (
 SELECT nome_fotos, img, id_cliente, id_fot FROM alboom 
 WHERE id_fot = '$getid' ORDER BY id_cliente ASC
) AS alboom GROUP BY id_cliente");

  • You want to sort the photos by id_client, right? Subselect, only it works? Could you attach a print of the output with query executed?

  • It’s kind of hard because my print screen boot has been broken I can’t print the screen but I’ll run the command on Workbench and lap it here can be?

  • Yes, edit the question with the result you are getting.

  • it always takes the first independent id of the clause if I put ASC he gets the first line if I put DESC he returns me the same line

  • in my query I made with the code it returns me the data of the second id and not the first independent if it is ASC or DESC

  • edited the question

Show 3 more comments

Browser other questions tagged php mysql mysqli

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