w3resource

AdventureWorks Database: Return the name of the product that is the least expensive

SQL Query - AdventureWorks: Exercise-102 with Solution

102. From the following table write a query in SQL to return the name of the product that is the least expensive in a given product category. Return name, list price and the first value i.e. LeastExpensive of the product.

Sample table: production.Product


Click to view Full table

Sample Solution:

SELECT Name, ListPrice,
       FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product
WHERE ProductSubcategoryID = 37;

Sample Output:

name               |listprice|leastexpensive     |
-------------------+---------+-------------------+
Patch Kit/8 Patches|     2.29|Patch Kit/8 Patches|
Road Tire Tube     |     3.99|Patch Kit/8 Patches|
Touring Tire Tube  |     4.99|Patch Kit/8 Patches|
Mountain Tire Tube |     4.99|Patch Kit/8 Patches|
LL Road Tire       |    21.49|Patch Kit/8 Patches|
LL Mountain Tire   |    24.99|Patch Kit/8 Patches|
ML Road Tire       |    24.99|Patch Kit/8 Patches|
Touring Tire       |    28.99|Patch Kit/8 Patches|
ML Mountain Tire   |    29.99|Patch Kit/8 Patches|
HL Road Tire       |     32.6|Patch Kit/8 Patches|
HL Mountain Tire   |       35|Patch Kit/8 Patches|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Calculate salary percentile for each employee for specific department.
Next: Return the employee with the fewest number of vacation hours.


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

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

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