> Code Storage: sub query

Code Storage

A Technical blog for those who want to gain knowledge about SQL,MySQL,Data Science and real time query

Responsive Ads Here

Advertisement

Showing posts with label sub query. Show all posts
Showing posts with label sub query. Show all posts

Monday, January 13, 2020

how to find nth highest salary| max salary different ways ?

nth highest salary,highest salary,max,
                                                     1.how to find nth highest salary| max salary
                                                                                                       different ways

How to find max salary
 Syntax:- select max(col_name) from table_name
 Example:select max(salary) from employee   

How to find 2nd highest salary
Example:- select max(salary) from employee
                   where salary<(select max(salary) from employee)

How to find top 2 highest salary
Example:- select distinct top 2 salary
                  from employee
                  order by salary desc

How to find nth highest salary using sub query
Example:- select top 1 salary from 
                  (select distinct top 1 salary
                   from employee
                   order by salary desc)
                   result
                   order by salary 

Here we used the sub query to find the nth highest salary but above query gives the top first salary but you needs 2nd,3nd,4nd,5nd...............salary we need to modify the query like this.you need 3nd highest salary then put  distinct top 3.....

 Example:-select top 1 salary from 
                   (select distinct top 1..(2nd,3nd,4nd,5nd....nth) salary
                   from employee
                   order by salary desc)
                   result
                   order by salary 


SEVER TYPE IN SSMS ( Sql Server Managment studio)

When you install the SQL Server management studio and then you want to connect with the SQL Server then there are four types of SQL servers ...