Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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.
replace string
Movies
Stored procedure
Exp8Q2
NAMES CODE
mysqltest
Time Difference Calculation (amended v2)
exp 8 XIV
Manish SQL
Names
Please log in to post a comment.