Find nth highest value using SQL Queries

Let us see how to calculate the first, second and the nth highest value.

Our employee table is as shown below.

Employee

Employee ID	Salary
1	4000
2	2500
3	4500
4	1800

To calculate the highest salary and the second highest

SELECT sal.employeeid, sal.salary FROM
   (select employeeid, salary from employee order by salary desc) sal
WHERE rownum <  3

EmployeeID        Salary
3                          4500
1                          4000

To calculate 2nd highest salary

SELECT max(salary)"Second Max Salary" FROM employee
where salary not in (select max(salary) from employee)

Second Max Salary
4000

To calculate nth highest salary(Using Rank() function) 

Select EmployeeID, Salary, RANK() OVER (ORDER BY salary desc) "RANK" from employee

EmployeeID      Salary          Rank
 3                       4500                 1
1                        4000                 2
2                        2500                 3
4                        1800                 4

In the next blog, let us apply these queries to a real data set and analyze the results.

Who works the hardest?

Recently, one of the Republican candidates made a statement about Americans - "People need to work longer hours to achieve 4% growth in our nation's economy". Really? I was curious to see how many hours did an average American work compared to his counterparts.  I downloaded data provided by International Labor Organization. Data is available in a csv format. I have loaded this data into 'R'.  Let us analyze this data set and see where we stand.

Let us see if there are any countries working more than 40 hrs a week.

Countries working more than 40 hrs/week

In Korean Republic (South Korea), people work more than 50hrs a week . Asian countries like Malaysia,Thailand,Hong Kong China and Bangladesh all work close to 45 hrs per week .
 
Let us see which countries are working between 35 and 40 hrs per week.

Countries working between 35 and 40 hrs/week

Our country of interest, United States, works 35 hrs per week which is in par with several other developed countries. Not sure what our candidate is taking about.

Japan's median working hours is only 36.5 hrs per week. I thought they worked harder. Something is not right. Well to verify, let us just extract Japan's data over the years and plot it instead of just taking the median value.

That's interesting. Japanese have started working less number of hours since 1990. In fact, they work less than the United States.

Now, I am very curious to know which countries work least. Let us plot the data for working hrs between 25 and 30.

Countries with least number of working hours/week

 Friends, maybe it is time to move to one of these these countries. What do you say?