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

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

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

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