How to find highest salary in sql

 

udayarumilli_highest

How to find highest salary in sql

We had a situation where we have to find out all the details of a product with the Nth highest price Note: Some of the products are in the same price range.

Here I am demonstrating with employee table. Create the table “Employee” from the below script.

Now let’s check the employee salaries using the below query.

From the table we can say that highest salary is: 120000 and lowest salary is: 69000

Now we‘ll see the different options how can get the employee details for Nth highest salary.

There are mainly two ways we can accomplish this as below. We’ll find the 3rd highest salary.

From the data yes we can confirm these are giving the 3rd highest salaried employee details.

 From the above queries if you want to find the 4th highest just replace 3 with 4 and if Nth highest required replace 3 with N.

 Now we’ll try to find out the 6th highest salary using the same queries.

Here is the problem. If you can observe there are two employees with the same salary

But these queries are retrieving only one record that is 6th row: Judy Lew. But there are two employees with the same salary which is 6th highest in the group.

If we try to capture the 7th highest it gives us the wrong result. It might be 6th row or 7th row depends on the ORDER BY clause.  To avoid this we have to change the query to use DENSE_RANK() instead of ROW_NUMBER().

Now we can see all the employees who are at the 6th highest salary are included in the result set.

Posted in SQL Development | Tagged , , , , , , , , | 2 Comments

Leave a Reply

2 Comments on "How to find highest salary in sql"

Notify of
avatar
Sort by:   newest | oldest | most voted
e-pvp
Guest

Wow, wonderful blog layout! How lengthy have you been running a blog for? you make running a blog look easy. The total look of your site is fantastic, let alone as} the content!

ddos vps
Guest

Sweet blog! I found it while searching on Yahoo News. Do you have any suggestions on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Appreciate it|

wpDiscuz