Tchello Posted January 3, 2012 Report Share Posted January 3, 2012 Preciso executar esta query:SELECT MONTH(data), YEAR(data) FROM tabelaWHERE data IS NOT NULL GROUP BY MONTH(data)ORDER BY data DESCPara que me liste:01/201212/201111/201110/201109/2011Mas dá erro:Column 'tabela.data' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Quote Link to comment Share on other sites More sharing options...
0 Vinicius Schuh Posted January 3, 2012 Report Share Posted January 3, 2012 (edited) Boa Tarde Tchello, acrescente no group by também a cláusula YEAR(Data)Ex: SELECT MONTH(Data) As Mes, YEAR(Data) As Ano FROM Tabela GROUP BY MONTH(Data), YEAR(Data) ORDER BY MONTH(Data), YEAR(Data)OBS: Dessa forma ira retornar duas colunas, uma com os valores do MES e outra com os valores do ANO.Espero ter ajudado. Até + Edited January 3, 2012 by Vinicius Schuh Quote Link to comment Share on other sites More sharing options...
0 Tchello Posted January 3, 2012 Author Report Share Posted January 3, 2012 Já tentei assim, mas olha o resultado:1 20121 20112 20113 20114 20115 20116 20117 20118 20119 201110 201111 201112 2011Orderna o ano ok de forma decrescente, porém o mês fica crescente. E preciso que ordene ano e mês de forma descrescente como no modelo acima. Quote Link to comment Share on other sites More sharing options...
0 Marcelo_2 Posted January 4, 2012 Report Share Posted January 4, 2012 (edited) ahe.. na verdade, assim n vai rolar nunca..porque 1 banco de dados só ordena pela 2ª coluna quando há "empate" (uma coincidencia) na 1ª..e c/ o group by esse empate não vai rolar, ok?se tiver 1 coluna chave primaria autonumerada (identity) ordena por ela desc q ai sim da beleza.. falou.. fui..obs: a ordem rola, não o group, ok? Edited January 4, 2012 by Marcelo_2 Quote Link to comment Share on other sites More sharing options...
0 Tchello Posted January 4, 2012 Author Report Share Posted January 4, 2012 Isso aí, xará... não tinha me atentado para a chave primaria... valeu... Quote Link to comment Share on other sites More sharing options...
Question
Tchello
Preciso executar esta query:
SELECT MONTH(data), YEAR(data) FROM tabela
WHERE data IS NOT NULL GROUP BY MONTH(data)
ORDER BY data DESC
Para que me liste:
01/2012
12/2011
11/2011
10/2011
09/2011
Mas dá erro:
Column 'tabela.data' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Link to comment
Share on other sites
4 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.