Search code examples
databaseoracle-databasesubquerycorrelated

Oracle correlated subquery, filter results

EDIT WITH SOLUTION: First of all, thanks to @Nick for finding the solution that you can see below. Second, I attach the code needed to execute the query as correlated.

SELECT L.num_pedido, PR.codigo, PR.nombre, PR.precio AS "Producto más caro"
FROM lineas L, productos PR
WHERE L.producto=PR.codigo
    AND (PR.precio) = (
                    SELECT MAX(PR2.precio)
                    FROM lineas L2, productos PR2
                    WHERE L2.producto=PR2.codigo
                        AND L2.num_pedido=L.num_pedido
                    )
ORDER BY L.num_pedido;

I have a problem with an apparently simple exercise. I show you first the tables involved:

enter image description here

The exercise asks: "Data of the most expensive product of each order (with a correlated query)"

Apparently, after searching this website I quickly found the solution, the problem is that I think I still do not execute the solution correctly. This is my code:

SELECT DISTINCT L.num_pedido, PR.codigo, PR.nombre, PR.precio AS "Max. precio unidad"
FROM lineas L, productos PR
WHERE L.producto=PR.codigo
    AND (precio) = (
                    SELECT MAX(precio)
                    FROM productos PR2
                    WHERE PR.codigo=PR2.codigo
                    )
ORDER BY L.num_pedido

But still I can not get it to filter by "num_pedido" (order number).The resulting table should look like this, omitting the rows crossed out in red:

enter image description here

enter image description here

Tables:

CREATE TABLE PRODUCTOS (
CODIGO  NUMBER(5,0) PRIMARY KEY,
NOMBRE  VARCHAR2(30) NOT NULL,
PRECIO   NUMBER(7,2) NOT NULL
);

CREATE TABLE  LINEAS    (   
NUM NUMBER(2,0), 
NUM_PEDIDO NUMBER(5,0), 
PRODUCTO NUMBER(5,0) NOT NULL , 
CANTIDAD NUMBER(8,0) NOT NULL , 
IMPORTE NUMBER(6,2), 
 CONSTRAINT DETALLE_PK PRIMARY KEY (NUM, NUM_PEDIDO) , 
 CONSTRAINT PEDIDO_FK FOREIGN KEY (NUM_PEDIDO)
  REFERENCES  PEDIDOS (NUM) , 
 CONSTRAINT PRODUCTO_FK FOREIGN KEY (PRODUCTO)
  REFERENCES  PRODUCTOS (CODIGO) 
   );

Data:

INSERT INTO PRODUCTOS VALUES (10001, 'PANTALÓN', 50);
INSERT INTO PRODUCTOS VALUES (10002, 'PANTALÓN PITILLO', 60);
INSERT INTO PRODUCTOS VALUES (10003, 'PANTALÓN CAMPANA', 55);
INSERT INTO PRODUCTOS VALUES (20001, 'CAMISA M/L', 65);
INSERT INTO PRODUCTOS VALUES (20002, 'CAMISA M/C', 45);
INSERT INTO PRODUCTOS VALUES (30001, 'VESTIDO C', 80);
INSERT INTO PRODUCTOS VALUES (30002, 'VESTIDO L', 90);
INSERT INTO PRODUCTOS VALUES (40001, 'FALDA LARGA', 50);
INSERT INTO PRODUCTOS VALUES (40002, 'FALDA CORTA', 45);
INSERT INTO PRODUCTOS VALUES (40003, 'FALDA MINI', 40);

INSERT INTO LINEAS VALUES (1,1, 10001, 2, 100);
INSERT INTO LINEAS VALUES (2,1, 30001, 1, 80);
INSERT INTO LINEAS VALUES (3,1, 20001, 2, 130);
INSERT INTO LINEAS VALUES (1,2, 20001, 1, 65);
INSERT INTO LINEAS VALUES (2,2, 40003, 3, 120);
INSERT INTO LINEAS VALUES (1,3, 40002, 2, 180);
INSERT INTO LINEAS VALUES (1,4, 10001, 2, 100);
INSERT INTO LINEAS VALUES (1,5, 20002, 2, 90);
INSERT INTO LINEAS VALUES (2,5, 40002, 1, 45);
INSERT INTO LINEAS VALUES (1,6, 40002, 1, 45);
INSERT INTO LINEAS VALUES (1,7, 40002, 1, 45);
INSERT INTO LINEAS VALUES (1,8, 40002, 1, 45);
INSERT INTO LINEAS VALUES (1,9, 40003, 1, 40);
INSERT INTO LINEAS VALUES (2,9, 20002, 1, 45);
INSERT INTO LINEAS VALUES (1,10,20002, 2, 90);

I know it's probably a very simple error, but I can not find the solution.

Thanks for your time.

Solution

  • This now works:

    SELECT A.NUM_PEDIDO, A.CODIGO, A.NOMBRE, A.PRECIO FROM (
  SELECT L.NUM_PEDIDO, PR.CODIGO, PR.NOMBRE, PR.PRECIO,RANK() OVER (PARTITION BY L.NUM_PEDIDO ORDER BY PR.PRECIO DESC) RANKORDER
  FROM LINEAS L INNER JOIN PRODUCTOS PR ON L.PRODUCTO =PR.CODIGO
)A WHERE RANKORDER = 1