Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Max Value based on two columns

Tags:

sql

oracle

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

enter image description here

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

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

like image 455
Miguel Avatar asked Oct 29 '22 13:10

Miguel


1 Answers

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.

like image 182
Gurwinder Singh Avatar answered Nov 15 '22 06:11

Gurwinder Singh