Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
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.
SQL_Joins_RankingFunctions
gfhrey
customer data
QLSV
Stored Procedure
Time Difference Calculation (amended v2)
ASSESSMENT2
prog1
QLSV
StackOverflow_53753663
Please log in to post a comment.