Thursday, November 7, 2013

SQL Rank...

There was a situation that Suppliers needs to be ranked by the lowest Rate given for the Items in a particular Year. It can be easily done by using SQL Rank function.
Sample query is as below.

   1: SELECT YearKey,SupplierKey,ItemKey,
   2: RANK() OVER (PARTITION BY ItemKey,YearKey ORDER BY AvgRate ASC) AS RankByAverageRate
   3:   FROM FactPurchases
   4: GROUP BY YearKey,SupplierKey,ItemKey

This will rank the Suppliers using the AvgRate of the Item available for the Year.

No comments:

Post a Comment