GROUP BY and ORDER BY Mysql

Asked

Viewed 4,554 times

-2

When I send the Query:

SELECT * FROM tabela WHERE id_cliente = '$id_cliente' ORDER BY ano DESC

Duplicated values are returned because there are duplicate values in the table. But I want to return only once each year contained in the table.

Failed to submit query’s below:

SELECT * FROM tabela WHERE id_cliente = '$id_cliente' GROUP BY ano ORDER BY ano DESC

SELECT * FROM tabela WHERE id_cliente = '$id_cliente' ORDER BY ano DESC GROUP BY ano

 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in [...]

Picture of the bank. inserir a descrição da imagem aqui

Table structure: here

php mysql sql mysqli

  • Usa distinct... SELECT DISTINCT * FROM table WHERE id_client = '$id_client' ORDER BY year DESC

  • @Thiagoaraújo continued the same thing, I will edit the question and with the attachment of the database!

  • For the mistake you’re making, it’s not in your query, your first query is correct. Try to run it on PHPMyAdmin directly. Also put all your code php to analyze.

  • @Robertofagundes even in Phpmyadmin continued to bring the results with the doubled year

  • Take a print of query which is running and the result in Phpmyadmin

  • http://imgur.com/zCdTuhC

  • Question, do you want to bring only one user record? Or do you want to bring every year of the user without repeating?

  • every year of the user without repeating

  • 1

    https://answall.com/questions/28184/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in

  • SELECT id, id_client, mes, status,ano FROM programacao_cliente_mes WHERE id_client = 2 GROUP BY mes, ano ORDER BY ano DESC

Show 5 more comments

4 answers

1

To bring all years of user without repeating, you can do so:

SELECT DISTINCT ano, id, id_cliente, mes, status
FROM programacao_clientes_mes
WHERE id_cliente = 2
ORDER BY ano DESC

  • http://imgur.com/bUEpVrp

  • Try parentheses after DISTINCT as I changed in the answer

  • unsuccessfully Roberto, http://imgur.com/8lDMsYB

  • You know what version of your Mysql ?

  • Server version: 5.7.14 - Mysql Community Server (GPL)

  • I made a new change, I put the DISTINCT ano as first field, see if it works now

  • Roberto, still with duplicate data http://imgur.com/yxTkiLG

  • Runs that query: SELECT DISTINCT(ano) ano, id, id_cliente, mes, status, LENGTH(ano)
FROM programacao_clientes_mes
WHERE id_cliente = 2
ORDER BY ano DESC and show me the result

  • http://imgur.com/Fp56eT5

  • 1

    Let’s go continue this discussion in chat.

  • Show me the result of this query: SELECT id, id_cliente, mes, STATUS, ano
FROM programacao_clientes_mes
WHERE id_cliente = 2
GROUP BY ano
ORDER BY ano DESC

  • I’ll send you a chat!

  • I’ll wait for her

  • http://imgur.com/euJeo8Z

  • Execute this command SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); in Phpmyadmin and then run again select previous and post here the result.

  • Roberto, for a moment was functional, but adding another record returned to "duplicate"

  • Show the result

  • http://imgur.com/wQdDP5V

  • Probably every time you make the connection to the database, you will need to run this query: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Show 14 more comments

0

If you do so by grouping by ID and year should come the expected result

SELECT  * FROM tabela WHERE id_cliente = '$id_cliente' GROUP BY ano,id_cliente ORDER BY ano,id_cliente DESC

  • unfortunately it was not. I continue a Query warning!

  • Try: SELECT * FROM table WHERE id_client = '". $id_client." ' GROUP BY year ORDER BY year ASC

  • What do you have in the $id_client? Put the whole code, it gets better to evaluate.

  • André in $id_client I have a string = "2" ! the code is part of a function of a class, I will post it

0

The query order is first GROUP BY and then ORDER BY

That would be SELECT * FROM tabela WHERE id_cliente = '$id_cliente' GROUP BY ano ORDER BY ano DESC

  • as quoted in the question, this query gives me a warning! #1064 - You have a syntax error in your SQL next to 'GROUP BY ano LIMIT 0, 25' on line 1

  • 1

    Put the complete code that is called farm. the problem is giving in mysqli seems to be query formation, but if it is not can be something else, that can not see without the code. , and sends the complete error too, it is with [...]

0

I’d make the next shape: 

SELECT id, id_cliente, mes, status, ano FROM programacao_clientes_mes WHERE id_cliente = 2 GROUP BY mes, ano ORDER BY ano DESC

  • André, without success. http://imgur.com/Y1x5qSb

  • Cara, put the structure of your table... export it and post its structure, from CREATE TABLE

  • http://pagebin.com/DvzDdHP9

  • Delete the id field and put it id, int, 10, auto_increment, Primary, to see... the error I think is there!

  • The error still persists!

Browser other questions tagged php mysql sql mysqli

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