SQL Server 2012 Write a SELECT statement with a ranking function to return produ

ID: 3592643 • Letter: S

Question

SQL Server 2012

Write a SELECT statement with a ranking function to return product data in five

columns: ProductID, SupplierID, UnitPrice, CategoryID, and 'Price Rank by Supplier'.

We want to rank products by assigning a continuous unique rank value from 1, 2, 3,

....etc. and display them in the 5th column. Ranking should base on each product's

UnitPrice, plus, products of different suppliers must be the ranked independently.

That means supplier 1 has its products ranked from 1, 2, 3, .... based on their

UnitPrice and supplier 2 also ranks its products from i, 2, 3, .... using the same

ranking criteria.

In case two products of the same supplier have identical UnitPrice such as products

34 and 67, both have a supplier 16 and have the same price $14.00, the one with larger

ProductID should be printed first.

Hint: Correct output should contain products of supplier 15 and 16 ranked as below.

They are only a portion of the output. There are many other rows before and

after these six rows.

What it should look like upon output

select ProductID, SupplierID, UnitPrice, CategoryID,

row_number() over(partition by SupplierID

order by UnitPrice desc, ProductID desc) as 'Price Rank by Supplier'

from Products;

--or, using RANK() for the same output

select ProductID, SupplierID, UnitPrice, CategoryID,

RANK() over(partition by UnitPrice

order by UnitPrice desc, ProductID desc) as 'Price Rank by Supplier'

from Products;

ProductID SupplierID UnitPrice CategoryID Price Rank by Supplier 69

36.00 4 1 71 15 21.50 4 2 33 15 2.50 4 3 35 16 18.00 1 1 67 16 14.00 1 2 34 16 14.00 1 3

select ProductID, SupplierID, UnitPrice, CategoryID,

row_number() over(partition by SupplierID

order by UnitPrice desc, ProductID desc) as 'Price Rank by Supplier'

from Products;

--or, using RANK() for the same output

select ProductID, SupplierID, UnitPrice, CategoryID,

RANK() over(partition by UnitPrice

order by UnitPrice desc, ProductID desc) as 'Price Rank by Supplier'

from Products;

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.