Window Funtions - RANK(), Sql Server

Window Funtions - RANK()

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

SELECT @@version AS 'sql server version'

CREATE TABLE Products (
    ProductID int,
    ProductName varchar(255),
    ProductCategory varchar(255),
    ProductPrice decimal(10)
    )
    
INSERT INTO Products (
    ProductID, ProductName, ProductCategory, ProductPrice)
VALUES
    (1, 'iPhone X',         'Technology',  1200),
    (2, 'Nike Vapor Shoes', 'Apperral',     180),
    (3, 'Ford Focus',       'Automobile', 18500),
    (4, 'Nissan Altima',    'Automobile', 18500),
    (5, 'Astros Cap',       'Apparrel',      80),
    (6, 'Barcelona Jersey', 'Apparrel',      90),
    (7, 'Samsung Galaxy',   'Technology',   850),
    (8, 'Apple Watch',      'Technology',   320)

-- Full list
SELECT* FROM Products;

-- Ranked by Prices
SELECT
    ProductName,
    ProductCategory,
    ProductPrice,
    RANK() OVER(ORDER BY ProductPrice DESC) PriceRank
FROM Products
-- NOTE: look how first 2 products (Focus & Altima) are ranked both as 1 but (since they share same price)
--       but since rank keep counting upon occurrencies the third product (iPhone) is still ranked as 3

-- Ranked by Prices but partitioned by Category
SELECT
    ProductName,
    ProductCategory,
    ProductPrice,
    RANK() OVER(PARTITION BY ProductCategory ORDER BY ProductPrice DESC) PriceRank
FROM Products
-- NOTE: every category has it's own rank


run \| edit \| history \| help	0

Please log in to post a comment.