﻿ AdventureWorks: Using rank function with order by clause

# 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

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
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     |
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿