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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With