CODEWITHSUNDEEP
oracle-database
user1205746
user1205746

Reputation: 3376

Get max date oracle

I have an oracle select statement that yields the results that I do not expect and I do not have an explanation to it. I would appreciate it if someone could point out what is wrong with my statement.

I have a simple table that consists of 3 fields: Date, Type and Id. I would like to list the latest date per Type and Id.

Below is the script to create my test data:

  CREATE TABLE "TEST3" 
   (    "Date" DATE, 
    "Type" VARCHAR2(20 BYTE), 
    "Id" NUMBER
   ) ;

Insert into TEST3 ("Date","Type","Id") values (to_date('11-MAR-12','DD-MON-RR'),'A',1);
Insert into TEST3 ("Date","Type","Id") values (to_date('12-SEP-15','DD-MON-RR'),'A',2);
Insert into TEST3 ("Date","Type","Id") values (to_date('05-APR-10','DD-MON-RR'),'A',2);
Insert into TEST3 ("Date","Type","Id") values (to_date('03-OCT-12','DD-MON-RR'),'B',2);

This is the statement I used:

SELECT MAX(c."Date"),  c."Type",  c."Id"
FROM Test3 c
GROUP BY   c."Date",c."Id",  c."Type"
ORDER BY c."Id",  c."Type"

The results I got are:

enter image description here

The second row should not be in the results, not sure why it is there. What is wrong with my statement?

enter image description here

Upvotes: 1

Views: 69

Answers (2)

Glenn
Glenn

Reputation: 9170

Sorry, misread your question. Take c."Date" out of the GROUP BY clause.

Upvotes: 1

Levesque
Levesque

Reputation: 943

If you don't want Id in the results then don't select it and don't group by it.

If you want it in the results, then you need to select either the min or max value, whichever you want to see (or sum it, you'll need to use some kind of group function). Either way, leave it out of the group by clause.

eg)

SELECT MAX(c."Date"),  c."Type",  MIN(c."Id")
FROM Test3 c
GROUP BY   c."Type"
ORDER BY c."Type"

Upvotes: 0

Related Questions