Query to return only higher values

Asked

Viewed 1,700 times

2

I have the following table:

Contract | Code | Year
13 7800 2015
12 7800 2014
12 7800 2013
12 7801 2016
12 7801 2013
12 5102 2017

I am trying to make a query to return the result below, that is, the largest "year" for each combination of "code" and "contract", but only stating the contract number. If you wanted to filter the table for the "12" contract, the result would have to be:

Contract | Code | Year
12 7800 2014
12 7801 2016
12 5102 2017

mysql query
  • 1

    Could you give me more details than you’d like? It wasn’t very clear.

  • @Marconi, I changed the question. I was confused, now I think comparing the two examples is easier.

  • @Dherik edited the question, I think it’s now clearer.

2 answers

3


You should have mentioned which query you used, but try:

SELECT contrato, MAX(ano) AS 'max_ano' FROM c GROUP BY contrato;

or:

SELECT contrato, MAX(ano) AS 'max_ano' FROM c GROUP BY contrato ORDER BY max_ano DESC LIMIT 1;

Bank created with:

CREATE TABLE "c" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "contrato" INTEGER NOT NULL,
    "codigo" INTEGER NOT NULL,
    "ano" INTEGER NOT NULL
);
INSERT INTO "c" VALUES(1,13,7800,2015);
INSERT INTO "c" VALUES(2,12,7800,2014);
INSERT INTO "c" VALUES(3,12,7800,2013);
INSERT INTO "c" VALUES(4,12,7801,2016);
INSERT INTO "c" VALUES(5,12,7801,2013);
INSERT INTO "c" VALUES(6,12,5102,2017);

Some tests:

sqlite> SELECT * FROM c;
1|13|7800|2015
2|12|7800|2014
3|12|7800|2013
4|12|7801|2016
5|12|7801|2013
6|12|5102|2017
sqlite> SELECT contrato, MAX(ano) AS 'max_ano' FROM c GROUP BY contrato;
12|2017
13|2015
sqlite> SELECT contrato, MAX(ano) AS 'max_ano' FROM c GROUP BY contrato ORDER BY max_ano DESC LIMIT 1;
12|2017
sqlite>

  • I have edited the question, I think it has now become clear what is intended. However the question is about how to query, so I don’t have one, but thanks for trying.

  • 2

    @Filipemoraes This answer is correct, to get the result you want simply add the code field on group by (beyond the filter, of course): SELECT contrato, codigo, MAX(ano) AS 'max_ano' FROM c WHERE contrato = 12 GROUP BY contrato, codigo;

  • @mgibsonbr you are right!

  • 1

    I just added the "code" field (in addition to the filter) and solved the problem.

-1

It’s not clear what you want, so I’ll cover the two ways I understand your question.

To get the longest year according to the specific contract code, use:

SELECT * FROM Tabela WHERE Contrato = 12 ORDER BY Ano DESC LIMIT 1

If you want the contract with the largest year, use:

SELECT * FROM Tabela ORDER BY Ano DESC LIMIT 1

  • I edited the question, I think that now it became clearer the intended.

Browser other questions tagged mysql query

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