Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
Window Funtions - RANK()
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
--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
View schema
edit mode
|
history
|
discussion
stackse - search stackoverflow differently