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
TUAN 7_QLDT
Libros
Add JSON"Valuetypes to XML Nodes using SQL
PracticeDB
SQL HW 1 Appline
Hack_this
Stored Procedure
test
Demo
student