Run Code
|
API
|
Code Wall
|
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', '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; -- 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
IOU2
Sum then delete one row
check constraint
MyWall
Test if a string can be made with substrings!
social
securing critical sections with manual locks in sql server
zani
arp
DBMS 4/9/20