Window Functions - ROW_NUMBER(), Sql Server

Window Functions - ROW_NUMBER()

--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', 'Apparrel',     180),
    (3, 'Ford Focus',       'Automobile', 18500),
    (4, 'Nissan Altima',    'Automobile', 18500),
    (5, 'Astros Cap',       'Apparrel',      80),
    (6, 'Barcelona Jersey', 'Apparrel',      400),
    (7, 'Samsung Galaxy',   'Technology',   850),
    (8, 'Apple Watch',      'Technology',   320)

-- Full list
SELECT* FROM Products;

-- Ordered list by Prices
SELECT
    ProductName,
    ProductCategory,
    ProductPrice,
    ROW_NUMBER() OVER(ORDER BY ProductPrice DESC) PriceRank
FROM Products
-- NOTE: First 2 products (Ford & Nissan) has same price value but Ford Focus
--       is listed as 1 since F is alphabeticaly first tham N. IN contrast with
--       RANK() the ROW_NUMBER() function does not repeat order values

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


run \| edit \| history \| help	0

Please log in to post a comment.

Advertise here.