--
--    Copyright (c) 2009 Software Gems Pty Ltd
--    Derek Asirvadem  01 Dec 10
--
-- Quick Tutorial on Oracle etc RANK(); 
--    why it is not necessary;
--    and how to produce any RANK() yourself.  
--
-- There is no substitute for understanding exactly how to manipulate
-- data using SQL.  
--
-- Prerequisite: Understanding Subqueries/Scalars
--

CREATE TABLE StudentMark (
    StudentId INT NOT NULL PRIMARY KEY,
    Mark      INT NOT NULL
    )

INSERT INTO StudentMark VALUES (01, 55)
INSERT INTO StudentMark VALUES (02, 88)
INSERT INTO StudentMark VALUES (13, 77)
INSERT INTO StudentMark VALUES (24, 99)
INSERT INTO StudentMark VALUES (35, 66)
INSERT INTO StudentMark VALUES (46, 44)
INSERT INTO StudentMark VALUES (57, 82)
INSERT INTO StudentMark VALUES (68, 68)

SELECT  StudentId,
        Mark,
        Rank = ( SELECT COUNT(*)
            FROM StudentMark inner
            WHERE inner.Mark >= outer.Mark
            ) 
    FROM StudentMark outer

-- then add:

    ORDER BY Rank

-- Do not leave this exercise until you feel you understand
-- THIS use of COUNT() completely; 
-- AND you can code this for any of your tables.