> Code Storage: find

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 find. Show all posts
Showing posts with label find. Show all posts

Monday, January 13, 2020

find nth highest salary without using top in sql ?

Find nth highest salary without using top in SQL

highest salary,nth highest salary,top
                                                     1.Find nth highest salary without using top sql
           Here i would use the CTC (common table expression).and i will use a one building function like Dense_Rank() to find the highest salary without using top.

Example:-     with result as 
                      (
                         select salary, dense_rank() over (order by salary desc) as denserank
                 from employee
                     )
                        select salary
                        from result
                        where result.denserank= n ( 1st,2st,3st.........nth)

Here we can get nth highest salary by using this query but end of the query we need to put the nth place which we want like if we want 1 highest salary we need to put 1 and 2  highest salary then put  2 like that we can find nth highest salary in this query.
                                                           But in our table duplicate records are there then above query will not give the perfect result then we should go for the next query. but here we need to use top () function

Example:-  with result as 
                      (
                         select salary, dense_rank() over (order by salary descas denserank
                 from employee
                     )
                        select  top 1 salary
                        from result
                        where result.denserank= n ( 1st,2st,3st.........nth)

Here i have another method to find nth highest salary without using top () function.

Example:-     select * from employee
                       order by salary desc offset n
                       fetch next 1 only

Here we used the offset which mean it will left the record and it will give the next record like we put offset 3 then it will left the top three (3) record and it will give the 4th record. in this process we can also find the nth highest salary without using top () function.

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