w3resource

AdventureWorks Database: Return the employee with the fewest number of vacation hours

SQL Query - AdventureWorks: Exercise-103 with Solution

103. From the following table write a query in SQL to return the employee with the fewest number of vacation hours compared to other employees with the same job title. Partitions the employees by job title and apply the first value to each partition independently.

Sample table: HumanResources.Employee


Click to view Full table

Sample table: Person.Person


Click to view Full table

Sample Solution:

SELECT JobTitle, LastName, VacationHours,
       FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle
                                   ORDER BY VacationHours ASC
                                   ROWS UNBOUNDED PRECEDING
                                  ) AS FewestVacationHours
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
    ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY JobTitle;

Sample Output:

jobtitle                                |lastname         |vacationhours|fewestvacationhours|
----------------------------------------+-----------------+-------------+-------------------+
Accountant                              |Moreland         |           58|Moreland           |
Accountant                              |Seamans          |           59|Moreland           |
Accounts Manager                        |Liu              |           57|Liu                |
Accounts Payable Specialist             |Tomic            |           63|Tomic              |
Accounts Payable Specialist             |Sheperdigian     |           64|Tomic              |
Accounts Receivable Specialist          |Poe              |           60|Poe                |
Accounts Receivable Specialist          |Spoon            |           61|Poe                |
Accounts Receivable Specialist          |Walton           |           62|Poe                |
Application Specialist                  |Bueno            |           71|Bueno              |
Application Specialist                  |Bacon            |           72|Bueno              |
Application Specialist                  |Meyyappan        |           73|Bueno              |
Application Specialist                  |Berg             |           74|Bueno              |
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Return the name of the product that is the least expensive.
Next: Return the difference in sales quotas for a specific employee over previous years.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to request a random row in SQL?

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2:

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Database: SQL Server, PostgreSQL Server, MySQL

Ref: https://bit.ly/39n35HP

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook