Query to return only the highest values

Navigation
2

I have the following table:

  

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

I'm trying to do a query to return the result below, ie the biggest "year" for each combination of "code" and "contract", but only entering the contract number. If you wanted to filter the table for contract "12", the result would have to be:

  

Contract | Privacy Policy | Year
  12 7800 2014
  12 7801 2016
  12 5102 2017

    
asked by anonymous 01.03.2015 / 20:52

2 answers

3

You should have mentioned which query was 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>
    
01.03.2015 / 21:34
-1

It's not clear what you want, so I'll cover the two ways that I understood your question.

To get the highest year based on the specific contract code, use:

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

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

SELECT * FROM Tabela ORDER BY Ano DESC LIMIT 1

    
01.03.2015 / 21:12
SecurityException - Permission Error Duplicate data