Ranking in SQL

use demostart
CREATE TABLE StateInfo
(
StateId char(10) primary key,
StateName varchar(100),
CityName varchar(100),
CountryName varchar(100)
)


CREATE TABLE InventoryInfo
(
SL_No INT IDENTITY PRIMARY KEY,
InventoryMenName char(50),
StateId CHAR(10) FOREIGN KEY REFERENCES StateInfo(StateId),
InventoryDate DATE,
InventoryAmount MONEY
)

select  * from StateInfo
select * from InventoryInfo

--INSERTING CERTAIN RECORDS IN THE TABLES


INSERT INTO StateInfo Values
('R0001' , 'North' , 'Delhi' , 'India'),
('R0002' , 'East' , 'Delhi' , 'India'),
('R0003' , 'West' , 'Delhi' , 'India'),
('R0004' , 'Souh' , 'Delhi' , 'India')

--SELECTING THE RECORDS FROM StateInfo TABLE

SELECT * FROM StateInfo

--INSERTIN RECORDS IN InventoryInfo TABLE

select * from InventoryInfo
INSERT INTO InventoryInfo VALUES
('Nisha Singh' , 'R0001' , '01/02/2015',5000),

('Nisha Singh' , 'R0001' , '02/02/2015',3500),

('Nisha Singh' , 'R0001' , '03/02/2015',4000),

('Prince' , 'R0001' , '01/02/2015',5340),

('Prince' , 'R0001' , '02/02/2015',4300),

('Prince' , 'R0001' , '03/02/2015',5000),

('Prince' , 'R0002' , '01/02/2015',5980),

('Prince' , 'R0003' , '01/02/2015',8790),

('Prince' , 'R0004' , '01/02/2015',5000),

('Nisha Singh' , 'R0002' , '01/02/2015',9800),

('Nisha Singh' , 'R0003' , '01/02/2015',8760),

('Nisha Singh' , 'R0004' , '01/02/2015',34230),

('Nisha Singh' , 'R0004' , '02/02/2015',15000)
--------------------------------------------------------------------------
--Ranking Data
/*Syntax :
Select <Col_Name_List>, <RANKING_fUNCTION_NAME>() OVER (Order By <Col_Name> [ASC|DESC])
From <Table_Name>

*/

--ROW_NUMBER()s

select * from InventoryInfo
Select SL_No,InventoryMenName,InventoryAmount,ROW_NUMBER ()
OVER(Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo


Select SL_No,InventoryMenName,InventoryAmount,StateId,ROW_NUMBER ()
 OVER(partition by StateId Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo

--RANK()
Select SL_No,InventoryMenName,InventoryAmount,RANK ()
OVER(Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo

Select SL_No,InventoryMenName,InventoryAmount,StateId,RANK ()
OVER(partition by StateId Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo



--DENSE_RANK()
Select SL_No,InventoryMenName,InventoryAmount,DENSE_RANK ()
OVER(Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo

Select SL_No,InventoryMenName,InventoryAmount,StateId,DENSE_RANK ()
OVER(partition by StateId Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo


--NTILE( <Number_Of_Division> )
Select SL_No,InventoryMenName,InventoryAmount,NTILE (3)
 OVER(Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo


Select SL_No,InventoryMenName,InventoryAmount,StateId,NTILE (3)
OVER(partition by StateId Order By InventoryAmount Desc) as 'Rank_Value' from InventoryInfo


-------------------------------------------------------------------
Select Inventorymenname ,
NTILE (3)
OVER(Order By avg(InventoryAmount) Desc) as 'Rank_Value' from InventoryInfo
group by InventoryMenName