RANK FUNCTION

SIMPLE RANK FUNCTION:

CREATE TABLE STUDENT

  (

    S_ID        NUMBER(10),

    S_NAME      VARCHAR(30),

    TAMIL_MARKS NUMBER(10),

    ENGLISH_MARKS NUMBER(10),

    MATHS_MARKS NUMBER(10),

    SCIENCE_MARKS NUMBER(10),

    S_SCIENCE_MARKS NUMBER(10)

  );

  

  INSERT INTO STUDENT VALUES(1,'PANDIAN',35,45,55,65,75);

  INSERT INTO STUDENT VALUES(2,'MUTHU',37,47,57,67,77);

  INSERT INTO STUDENT VALUES(3,'ASHOK',40,50,60,70,80);

  

  INSERT INTO STUDENT VALUES(4,'KUMAR',33,43,53,63,73);

  INSERT INTO STUDENT VALUES(5,'RANA',36,46,56,66,76);

  

  

  SELECT * FROM STUDENT;

1 PANDIAN 35 45 55 65 75

2 MUTHU 37 47 57 67 77

3 ASHOK 40 50 60 70 80

4 KUMAR 33 43 53 63 73

5 RANA 36 46 56 66 76

 SELECT S_NAME,TOTAL,RANK() OVER (ORDER BY TOTAL DESC) AS CLASS_RANK

  FROM 

(SELECT S_NAME,(TAMIL_MARKS+ENGLISH_MARKS+MATHS_MARKS+SCIENCE_MARKS+SCIENCE_MARKS) TOTAL  FROM STUDENT);

ASHOK       290   1

MUTHU     275   2

RANA 270 3

PANDIAN 265 4

KUMAR 255   5

 DESC SALES;

Name   Null Type         

------ ---- ------------ 

S_ID        NUMBER(10)   

C_ID        NUMBER(10)   

P_ID        NUMBER(10)   

S_DATE      DATE         

S_TYPE      VARCHAR2(30) 

QTY         NUMBER(10)   

AMOUNT      NUMBER(7,2)  

SELECT * FROM SALES;

1 11 21 10-OCT-10 ONLINE 2 10000

2 12 23 10-NOV-10 OUTLET 1 1000

3 13 23 10-OCT-11 ONLINE 2 6000

4 14 24 10-DEC-11 ONLINE 1 500

5 15 25 10-OCT-12 OUTLET 1 1000

6 16 26 10-AUG-12 OUTLET 2 8000

SELECT RANK() OVER(ORDER BY SUM(AMOUNT) DESC) SALESRANK,TO_CHAR(S_DATE,'YYYY'),S_TYPE,SUM(AMOUNT)

FROM SALES

GROUP BY TO_CHAR(S_DATE,'YYYY'),S_TYPE;

1 2010 ONLINE 10000

2 2012 OUTLET 9000

3 2011 ONLINE 6500

4 2010 OUTLET 1000