Problem selecting max year, max month & max date in one query

CREATE TABLE TEMP
(
C_INVOICE_ID NUMBER(10),
DIA NUMBER,
MES NUMBER,
ANO NUMBER,
SOCIO_NEGOCIO NVARCHAR2(60) NOT NULL,
PRODUCTO_NOM NVARCHAR2(60) NOT NULL,
M_PRODUCT_ID NUMBER(10),
CATEGORIA NVARCHAR2(60) NOT NULL,
COSTO NUMBER
)

INSERT INTO TEMP VALUES(10111,1,2,2010,'1585','ALURON 100MG X 30 TABLETAS',1530,15,1.15);
INSERT INTO TEMP VALUES(1015,15,2,2010,'1520','ALURON 100MG X 30 TABLETAS',1530,15,2.15);
INSERT INTO TEMP VALUES(5654,5,2,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.2);
INSERT INTO TEMP VALUES(15321,4,6,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.8);
INSERT INTO TEMP VALUES(13548,8,6,2010,'1585','AMARYL 2MG X 15 TABLETAS',1531,15,4.3);
INSERT INTO TEMP VALUES(19456,31,4,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4);
INSERT INTO TEMP VALUES(116544,8,8,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.8);
INSERT INTO TEMP VALUES(132,2,3,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.1);
INSERT INTO TEMP VALUES(168,15,1,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.9);
INSERT INTO TEMP VALUES(4898,7,4,2010,'1220','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8);
INSERT INTO TEMP VALUES(15132,25,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.4);
INSERT INTO TEMP VALUES(1684,18,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.3);
INSERT INTO TEMP VALUES(14988,8,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,2.9);
INSERT INTO TEMP VALUES(84941,8,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.1);
INSERT INTO TEMP VALUES(1155,7,4,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.7);
INSERT INTO TEMP VALUES(184,1,1,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);
INSERT INTO TEMP VALUES(48994,8,4,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.7);
INSERT INTO TEMP VALUES(1465465,9,5,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8);
INSERT INTO TEMP VALUES(16,18,6,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);
INSERT INTO TEMP VALUES(894886,20,4,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);

CREATE TABLE TEMP
(
C_INVOICE_ID NUMBER(10),
DIA NUMBER,
MES NUMBER,
ANO NUMBER,
SOCIO_NEGOCIO NVARCHAR2(60) NOT NULL,
PRODUCTO_NOM NVARCHAR2(60) NOT NULL,
M_PRODUCT_ID NUMBER(10),
CATEGORIA NVARCHAR2(60) NOT NULL,
COSTO NUMBER
)

INSERT INTO TEMP VALUES(10111,1,2,2010,'1585','ALURON 100MG X 30 TABLETAS',1530,15,1.15);
INSERT INTO TEMP VALUES(1015,15,开发者_JAVA技巧2,2010,'1520','ALURON 100MG X 30 TABLETAS',1530,15,2.15);
INSERT INTO TEMP VALUES(5654,5,2,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.2);
INSERT INTO TEMP VALUES(15321,4,6,2010,'1520','AMARYL 2MG X 15 TABLETAS',1531,15,4.8);
INSERT INTO TEMP VALUES(13548,8,6,2010,'1585','AMARYL 2MG X 15 TABLETAS',1531,15,4.3);
INSERT INTO TEMP VALUES(19456,31,4,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4);
INSERT INTO TEMP VALUES(116544,8,8,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.8);
INSERT INTO TEMP VALUES(132,2,3,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.1);
INSERT INTO TEMP VALUES(168,15,1,2010,'1220','AMARYL 2MG X 15 TABLETAS',1531,15,4.9);
INSERT INTO TEMP VALUES(4898,7,4,2010,'1220','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8);
INSERT INTO TEMP VALUES(15132,25,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.4);
INSERT INTO TEMP VALUES(1684,18,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.3);
INSERT INTO TEMP VALUES(14988,8,8,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,2.9);
INSERT INTO TEMP VALUES(84941,8,9,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.1);
INSERT INTO TEMP VALUES(1155,7,4,2010,'1585','AMOXAL 250MG X 75ML SUSPENSION',1534,15,4.7);
INSERT INTO TEMP VALUES(184,1,1,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);
INSERT INTO TEMP VALUES(48994,8,4,2010,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.7);
INSERT INTO TEMP VALUES(1465465,9,5,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.8);
INSERT INTO TEMP VALUES(16,18,6,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);
INSERT INTO TEMP VALUES(894886,20,4,2009,'1520','AMOXAL 250MG X 75ML SUSPENSION',1534,15,3.9);

I have this query so far,

SELECT MAX (TEMP.COSTO) COSTO,
       TEMP.M_PRODUCT_ID,
       TEMP.ANO
  FROM TEMP TEMP
       INNER JOIN 
       (SELECT MAX(ANO) ANO, 
               M_PRODUCT_ID
          FROM TEMP I
        GROUP BY M_PRODUCT_ID
       ) LA ON 
       LA.ANO = TEMP.ANO 
   AND LA.M_PRODUCT_ID = TEMP.M_PRODUCT_ID
GROUP BY TEMP.M_PRODUCT_ID,
         TEMP.ANO;

but my intention is not to have the max cost, my intention is to query in this order

first i need to select distinct m_product_id then

for every m_product_id i need to

filter max (ano) then filter max (mes) then filter max (dia)

I need the result set like this.

C_INVOICE_ID DIA MES ANO SOCIO  PRODUCTO_NO                 M_PRODUCT_ID CATERGORIA COSTO
1015         15  2    2010 1520 ALURON 100MG X 30 TABLETAS     1530           15     2.15
5654         5   2    2010 1520 AMARYL 2MG X 15 TABLETAS       1531           15     4.2
15132        25  9    2010 1585 AMOXAL 250MG X 75ML SUSPENSION 1534           15     3.4

please i really apritiated any help, thanks pd:i'm using oracle 9i

If I am reading your requirement right, I come up with this:

select *
  from (select t.*
              ,row_number() over(partition by m_product_id 
                                     order by ano desc, mes desc, dia desc) as rn
          from temp t
       )
 where rn = 1;

But when I run the query I get another result than what you specified:

1015    15  2  2010  1520  ALURON 100MG X 30 TABLETAS       1530  15 2,15
116544   8  8  2010  1220  AMARYL 2MG X 15 TABLETAS         1531  15 4,8
15132   25  9  2010  1585  AMOXAL 250MG X 75ML SUSPENSION   1534  15 3,4

For product_id = 1531, I picked the row with invoice_id = 116544, because that row comes up first if I sort by the order you specified:

select c_invoice_id, ano, mes, dia
  from temp t 
 where m_product_id = 1531
order by ano desc, mes desc, dia desc; 

C_INVOICE_ID        ANO        MES        DIA
------------ ---------- ---------- ----------
      116544       2010          8          8
       13548       2010          6          8
       15321       2010          6          4
       19456       2010          4         31
         132       2010          3          2
        5654       2010          2          5
         168       2010          1         15

Did I missunderstand you requirements?

Try this:

SELECT a.*
  FROM (
         SELECT a.*, RANK() OVER(PARTITION BY m_product_id ORDER BY DIA,MES,ANO DESC) rnk
           FROM temp a
       ) a
 WHERE rnk=1