SQL> @&1 SQL> /* @pregBD_Piezas_rownum.sql */ SQL> SET ECHO ON SQL> SET LINESIZE 132 SQL> SET PAGESIZE 200 SQL> SQL> /* nombres de las piezas, y cantidad de cada tipo SQL> */ SQL> SELECT nombPieza "tipo", count(clvPieza) "total" 2 FROM Pieza 3 GROUP BY nombPieza; tipo total -------------------------------- ---------- PALANCA 1 TORNILLO 2 TUBO 1 TUERCA 3 formón 1 paño 1 6 filas seleccionadas. SQL> SQL> SELECT ROWNUM, pieza.* 2 FROM pieza; ROWNUM CLVPIEZA NOMBPIEZA COLOR ---------- ---------- -------------------------------- -------------------------------- 1 91 TUERCA ROJO 2 92 TUERCA VERDE 3 93 TORNILLO AZUL 4 94 TORNILLO ROJO 5 95 TUERCA AZUL 6 96 PALANCA 7 97 TUBO GRIS 8 77 paño amarillo 9 78 formón gris 9 filas seleccionadas. SQL> SQL> /* obtener los 4 primeros nombres de piezas (orden alfabético) */ SQL> /* NO ES CORRECTO */ SQL> SELECT ROWNUM, pieza.* 2 FROM pieza 3 WHERE ROWNUM <5 4 ORDER BY nombPieza; ROWNUM CLVPIEZA NOMBPIEZA COLOR ---------- ---------- -------------------------------- -------------------------------- 3 93 TORNILLO AZUL 4 94 TORNILLO ROJO 1 91 TUERCA ROJO 2 92 TUERCA VERDE SQL> SQL> /* observar el número de fila que se asigna al ordenar, como SQL> consecuencia de que la asignación de nombre se produce tras SQL> obtener las tuplas con el select .. where */ SQL> SELECT ROWNUM, pieza.* 2 FROM pieza 3 ORDER BY nombPieza; ROWNUM CLVPIEZA NOMBPIEZA COLOR ---------- ---------- -------------------------------- -------------------------------- 9 78 formón gris 6 96 PALANCA 8 77 paño amarillo 3 93 TORNILLO AZUL 4 94 TORNILLO ROJO 7 97 TUBO GRIS 1 91 TUERCA ROJO 5 95 TUERCA AZUL 2 92 TUERCA VERDE 9 filas seleccionadas. SQL> SQL> /* la solución es encapsular la consulta (en el FROM) para que la SQL> asignación del ROWNUM se efectúe después */ SQL> SQL> SELECT ROWNUM, PO.* 2 FROM (SELECT * 3 FROM pieza 4 ORDER BY nombPieza) PO; ROWNUM CLVPIEZA NOMBPIEZA COLOR ---------- ---------- -------------------------------- -------------------------------- 1 78 formón gris 2 96 PALANCA 3 77 paño amarillo 4 93 TORNILLO AZUL 5 94 TORNILLO ROJO 6 97 TUBO GRIS 7 91 TUERCA ROJO 8 95 TUERCA AZUL 9 92 TUERCA VERDE 9 filas seleccionadas. SQL> SQL> SELECT ROWNUM, PO.* 2 FROM (SELECT * 3 FROM pieza 4 ORDER BY nombPieza) PO 5 WHERE ROWNUM < 5; ROWNUM CLVPIEZA NOMBPIEZA COLOR ---------- ---------- -------------------------------- -------------------------------- 1 78 formón gris 2 96 PALANCA 3 77 paño amarillo 4 93 TORNILLO AZUL SQL> SQL> /* mostrar el ranking con los 2 nombres de piezas que las que hay más piezas */ SQL> SELECT ROWNUM, totNombP.* 2 FROM (SELECT nombPieza "tipo", count(clvPieza) AS total 3 FROM Pieza 4 GROUP BY nombPieza 5 ORDER BY total DESC) totNombP 6 WHERE ROWNUM < 3; ROWNUM tipo TOTAL ---------- -------------------------------- ---------- 1 TUERCA 3 2 TORNILLO 2 SQL> SQL> /*---------------------------------------------------------------*/ SQL> /* otro modo, usando secuencias */ SQL> CREATE SEQUENCE cont; Secuencia creada. SQL> SQL> CREATE TABLE rankingPiezas AS ( 2 SELECT cont.NEXTVAL orden, nombPieza, total 3 FROM (SELECT nombPieza, count(clvPieza) total 4 FROM Pieza 5 GROUP BY nombPieza 6 ORDER BY total DESC) 7 ); Tabla creada. SQL> SQL> SELECT * FROM rankingPiezas WHERE orden < 3; ORDEN NOMBPIEZA TOTAL ---------- -------------------------------- ---------- 1 TUERCA 3 2 TORNILLO 2 SQL> SQL> DROP TABLE rankingPiezas; Tabla borrada. SQL> DROP SEQUENCE cont; Secuencia borrada. SQL> /*---------------------------------------------------------------*/ SQL> /* utilizando la función analítica ROW_NUMBER */ SQL> SQL> /* mostrar el ranking con los 2 nombres de piezas que las que hay más piezas */ SQL> SELECT orden, totNombP.* 2 FROM (SELECT nombPieza "tipo", count(clvPieza) AS total, ROW_NUMBER() OVER (ORDER BY count(clvPieza) DESC) AS orden 3 FROM Pieza 4 GROUP BY nombPieza) totNombP 5 WHERE ROWNUM < 3; ORDEN tipo TOTAL ORDEN ---------- -------------------------------- ---------- ---------- 1 TUERCA 3 1 2 TORNILLO 2 2 SQL> SQL> /* mostrar las 2 piezas de cada tipo (seleccionadas del orden por color) */ SQL> SELECT orden, totNombP.* 2 FROM (SELECT nombPieza "tipo", color, ROW_NUMBER() OVER (PARTITION BY nombPieza ORDER BY color) AS orden 3 FROM Pieza) totNombP 4 WHERE orden < 3; ORDEN tipo COLOR ORDEN ---------- -------------------------------- -------------------------------- ---------- 1 PALANCA 1 1 TORNILLO AZUL 1 2 TORNILLO ROJO 2 1 TUBO GRIS 1 1 TUERCA AZUL 1 2 TUERCA ROJO 2 1 formón gris 1 1 paño amarillo 1 8 filas seleccionadas. SQL> SQL> SQL> set echo off fin de la ejecucion de K:\BD_2017\desarrollo\pruebas_ORACLE\BD_Piezas_nw\pregBD_Piezas_rownum.sql inicio de sesión interactiva ASIGBD vicious.hendrix-oracle.cps.unizar.es fecha: 30/03/17 SQL> exit