Browse Products with ranking function

  
SQL>
SQL>
SQL> CREATE TABLE Product (
  2  ProductID INT NOT NULL PRIMARY KEY,
  3  Name VARCHAR(50) NOT NULL,
  4  Description VARCHAR(1000) NOT NULL,
  5  Price NUMBER NULL,
  6  ImagePath VARCHAR(50) NULL,
  7  soldout NUMBER(1,0) NULL,
  8  Promotion NUMBER(1,0) NULL);

Table created.

SQL>
SQL> CREATE SEQUENCE ProductIDSeq;

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
  2  BEFORE INSERT ON Product
  3  FOR EACH ROW
  4  BEGIN
  5     SELECT ProductIDSeq.NEXTVAL
  6     INTO :NEW.ProductID FROM DUAL;
  7  END;
  8  /

Trigger created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
  2  VALUES ('Pen', 'Ball Pen',5.99, 'pen.jpg', 1, 0);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Ruler', 'Long',14.99, 'ruler.jpg', 0, 0);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Desk', 'Computer Desk',5.99, 'desk.jpg', 0, 1);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('PC', 'Notebook',49.99, 'pc.jpg', 0, 1);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Mouse', 'Wireless',9.99, 'mouse.jpg',  1, 0);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Keyboard', 'keyboard',3.75, 'keyboard.jpg', 0, 0);

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT ProductID, Name FROM(
  2  SELECT RANK() OVER (ORDER BY ProductID) As RanPeter, ProductID, Name
  3  FROM Product
  4  ORDER BY PRODUCTID
  5  )
  6  WHERE RanPeter BETWEEN 6 AND 10;

 PRODUCTID NAME
---------- --------------------------------------------------
         6 Keyboard

1 row selected.

SQL>
SQL> drop table product;

Table dropped.

SQL> drop sequence ProductIDSeq;

Sequence dropped.

Related examples in the same category

1.	RANK(): rank items, leaves a gap in the sequence when there is a tie
2.	The ranking as opposed to a row-numbering problem (the problem of ties)
3.	The WHERE is applied before the RANK()
4.	Rank with order by clause
5.	When RANK() is added to the statement, the RANK function is applied last, just before the ordering
6.	RANK() with NULLS LAST demo
7.	RANK() with NULLS FIRST
8.	Rank(): If the statement requests another ordering, another sort may result
9.	Rank() with null values
10.	Rank() over partition
11.	rank() over (partition by deptno order by sal desc )

Browse Products with ranking function : Rank « Analytical Functions « Oracle PL / SQL

Related examples in the same category