StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POProblem selecting max year, max month & max date in one query
text
Body
copied!<pre><code>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,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); </code></pre> <p>I have this query so far,</p> <pre><code>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; </code></pre> <p>but my intention is not to have the max cost, my intention is to query in this order</p> <p>first i need to select distinct m_product_id then</p> <p>for every m_product_id i need to</p> <p>filter max (ano) then filter max (mes) then filter max (dia)</p> <p>I need the result set like this.</p> <pre><code>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 </code></pre> <p>please i really apritiated any help, thanks pd:i'm using oracle 9i</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload