w3resource

AdventureWorks Database: Using rank function with order by clause

SQL Query - AdventureWorks: Exercise-34 with Solution

34. From the following table write a query in SQL to find those persons who lives in a territory and the value of salesytd except 0. Return first name, last name,row number as 'Row Number', 'Rank', 'Dense Rank' and NTILE as 'Quartile', salesytd and postalcode. Order the output on postalcode column.

Sample table: Sales.SalesPerson


Click to view Full table

Sample table: Person.Person


Click to view Full table

Sample table: Person.Address


Click to view Full table

Sample Solution:

SELECT p.FirstName, p.LastName  
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"  
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"  
    ,s.SalesYTD, a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

Sample Output:

firstname|lastname         |Row Number|Rank|Dense Rank|Quartile|salesytd    |postalcode|
---------+-----------------+----------+----+----------+--------+------------+----------+
Michael  |Blythe           |         1|   1|         1|       1|3763178.1787|98027     |
Linda    |Mitchell         |         2|   1|         1|       1|4251368.5497|98027     |
Jillian  |Carson           |         3|   1|         1|       1|3189418.3662|98027     |
Garrett  |Vargas           |         4|   1|         1|       1|1453719.4653|98027     |
Tsvi     |Reiter           |         5|   1|         1|       2| 2315185.611|98027     |
Pamela   |Ansman-Wolfe     |         6|   1|         1|       2|1352577.1325|98027     |
Shu      |Ito              |         7|   7|         2|       2|2458535.6169|98055     |
José     |Saraiva          |         8|   7|         2|       2|2604540.7172|98055     |
David    |Campbell         |         9|   7|         2|       3|1573012.9383|98055     |
Tete     |Mensa-Annan      |        10|   7|         2|       3|1576562.1966|98055     |
Lynn     |Tsoflias         |        11|   7|         2|       3|1421810.9242|98055     |
Rachel   |Valdez           |        12|   7|         2|       4|1827066.7118|98055     |
Jae      |Pak              |        13|   7|         2|       4|4116871.2277|98055     |
Ranjit   |Varkey Chudukatil|        14|   7|         2|       4|3121616.3202|98055     |

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Order table conditionally using case statement.
Next: Skip a number of rows from a sorted table.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

T-SQL Cast versus Convert?

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Ref : https://bit.ly/3VKT5M8