Max Value based on two columns

I have a table with multiple columns that contains both month and year. I’m trying to identify based on column1,column2 what is the greatest month and year.

I have a DDL for a test table as such.

CREATE TABLE "TEST" 
   (    "COLUMN1" VARCHAR2(6 BYTE), 
    "COLUMN2" VARCHAR2(6 BYTE), 
    "YEAR" NUMBER, 
    "MONTH" NUMBER
   );

This is the data in the example table.

REM INSERTING into TEST
SET DEFINE OFF;
Insert into TEST (COLUMN1,COLUMN2,YEAR,MONTH) values ('A','A',11,4);
Insert into TEST (COLUMN1,COLUMN2,YEAR,MONTH) values ('A','A',14,3);
Insert into TEST (COLUMN1,COLUMN2,YEAR,MONTH) values ('A','A',11,5);
Insert into TEST (COLUMN1,COLUMN2,YEAR,MONTH) values ('A','A',14,1);

I want to get the record with the max month and year. When I attempt to retrieve the record using this query

 SELECT t.COLUMN1, t.COLUMN2, max(t.YEAR), max(t.MONTH) 
 FROM TEST t 
 group by COLUMN1, COLUMN2;

I get this results

It appears to me that it is separating the max year and max month independently of the other columns. The expected result should be

how can I get down to the appropriate results? I have tried several self-joins to no avail.

Answer

You want to use Oracle’s built-in LAST (or FIRST) with MAX like this:

SELECT t.COLUMN1,
  t.COLUMN2,
  MAX(t.YEAR),
  MAX(t.MONTH) keep (dense_rank last ORDER BY YEAR nulls first) MONTH
FROM TEST t
GROUP BY COLUMN1,
  COLUMN2;

It will find the max of month in latest year only.

Advertisement

Answer