/* @pregBD_Piezas_rownum.sql */
SET ECHO ON
SET LINESIZE 132
SET PAGESIZE 200

/* nombres de las piezas, y cantidad de cada tipo
*/
SELECT nombPieza "tipo", count(clvPieza) "total"
 FROM   Pieza
 GROUP BY nombPieza;

SELECT ROWNUM, pieza.*
 FROM pieza;

/* obtener los 4 primeros nombres de piezas (orden alfabético) */
/* NO ES CORRECTO */
SELECT ROWNUM, pieza.*
 FROM pieza
 WHERE ROWNUM <5
 ORDER BY nombPieza;

/* observar el número de fila que se asigna al ordenar, como
   consecuencia de que la asignación de nombre se produce tras
   obtener las tuplas con el select .. where */
SELECT ROWNUM, pieza.*
 FROM pieza
 ORDER BY nombPieza;

/* la solución es encapsular la consulta (en el FROM) para que la
   asignación del ROWNUM se efectúe después */

SELECT ROWNUM, PO.*
 FROM (SELECT *
        FROM pieza
        ORDER BY nombPieza) PO;

SELECT ROWNUM, PO.*
 FROM (SELECT *
        FROM pieza
        ORDER BY nombPieza) PO
 WHERE ROWNUM < 5;

/* mostrar el ranking con los 2 nombres de piezas que las que hay más piezas */
SELECT ROWNUM, totNombP.*
FROM (SELECT nombPieza "tipo", count(clvPieza) AS total
      FROM   Pieza
      GROUP  BY nombPieza
      ORDER  BY total DESC) totNombP
WHERE ROWNUM < 3;

/*---------------------------------------------------------------*/
/* otro modo, usando secuencias */
CREATE SEQUENCE cont;

CREATE TABLE rankingPiezas AS (
  SELECT cont.NEXTVAL orden, nombPieza, total
  FROM (SELECT nombPieza, count(clvPieza) total
        FROM   Pieza
        GROUP  BY nombPieza
        ORDER BY total DESC)
);

SELECT * FROM rankingPiezas WHERE orden < 3;

DROP TABLE rankingPiezas;
DROP SEQUENCE cont;
/*---------------------------------------------------------------*/
/* utilizando la función analítica ROW_NUMBER */

/* mostrar el ranking con los 2 nombres de piezas que las que hay más piezas */
SELECT orden, totNombP.*
FROM (SELECT nombPieza "tipo", count(clvPieza) AS total, ROW_NUMBER() OVER (ORDER BY count(clvPieza) DESC) AS orden
      FROM   Pieza
      GROUP  BY nombPieza) totNombP
WHERE ROWNUM < 3;

/* mostrar las 2 piezas de cada tipo (seleccionadas del orden por color) */
SELECT orden, totNombP.*
FROM (SELECT nombPieza "tipo", color, ROW_NUMBER() OVER (PARTITION BY nombPieza ORDER BY color) AS orden
      FROM   Pieza) totNombP
WHERE orden < 3;