You have a database that contains the tables as shown in the exhibit. (Refer to the Exhibit.)
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet
the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order thatthey are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area.
A.
Explanation:
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.
UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
The query submitted as answer fulfills the requirements but I don’t understand why the “PARTITION BY ProductCatalog.UnitPrice” is necessary?
I would use the following query:
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
1
0
if you don’t partition by price,the same price will has difference rank.
so, i guess this is why use ‘partition by’ is necessary.
0
0
I think Riemi is right.
Whether using partition by clause or not, RANK() function will make the same price with the same rank.
If you want the same price with the different ranks, sequential ones, you should use ROW_NUMBER().
1
0
sorry,i tested it,actually you are right….the ‘partition by’ is unnecessary
1
0
I have the exact same problem with this answer.
It doesn’t make sense at all.
If you partition on UnitPrice, all the UnitPrices within a single partition will be the same! No use Ranking them.
Leaving out the Partition By clause will rank the prices throughout the table i would think and would make better sense.
A better scenario would be if you would want to know how the UnitPrices would rank within the Categories: OVER (PARTITION BY ProductCatalog.CatID ORDER BY ProductCatalog.UnitPrice DESC)
0
0
Hi! I’ve been following your blog for a long time now and
finally got the bravery to go ahead and give you a shout out from Huffman Texas!
Just wanted to mention keep up the great job! Quest
Bars blogesaurus
0
0
I’ll right away clutch your rss as I can not in finding your e-mail subscription hyperlink or newsletter service.
Do you have any? Kindly allow me understand so that I may just subscribe.
Thanks. Quest Bars blogesaurus
0
0
PARTITION BY is not necessary and also ORDER BY is redundant becuase RANK will sort the rows .
1
0
The two ORDER BY UnitPrice has different meaning. We need to keep them all.
Just delete ‘PARTITION BY UnitPrice’. It’s not make sense.
0
0
Select ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog..ProductID,ProductCatalog..ProductName,ProductCatalog..Unit Price, from sales.productCatalog,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
From SAles.ProductCatalog
order by productCatalog.unitprice desc
0
0
Here are a few of the web sites we advise for our visitors.
0
0
The info talked about in the write-up are a few of the very best accessible.
0
0
Here are several of the web-sites we advise for our visitors
0
0
check beneath, are some absolutely unrelated sites to ours, nevertheless, they are most trustworthy sources that we use
0
0
The details talked about inside the report are a number of the best readily available
0
0
very few sites that happen to become detailed beneath, from our point of view are undoubtedly very well worth checking out
0
0
check beneath, are some absolutely unrelated internet sites to ours, nevertheless, they may be most trustworthy sources that we use
0
0
Answer has to be like this.
SELECT ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProductID,
ProductCatalog.ProdName,
ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
1
0
we prefer to honor many other world-wide-web websites around the internet, even though they arent linked to us, by linking to them. Underneath are some webpages really worth checking out
0
0
Wonderful story, reckoned we could combine a number of unrelated information, nonetheless truly really worth taking a look, whoa did 1 study about Mid East has got a lot more problerms at the same time
0
0
Here are several of the web-sites we suggest for our visitors
0
0
I have a doubt! Do we have to reference Column name too with the two part reference? Or just the table name?
0
0