This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.
Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER – Query to Retrieve the Nth Maximum value
I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database Table
Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.
For particular example of employee :
How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table
The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary
where n > 1 (n is always greater than one)
Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks; GO SELECT TOP 1 Rate FROM ( SELECT DISTINCT TOP 4 Rate FROM HumanResources.EmployeePayHistory ORDER BY Rate DESC) A ORDER BY Rate GO
Reference : Pinal Dave (https://blog.sqlauthority.com), Pravin Phatangare
338 Comments. Leave new
Its very nice
Hi to all
this is siva prasad.in above some quereis were not worked properly ,in the sense i am unable to get desired output when some are duplicated(sal is repeated for some records).
Finally I executed a query .It doesn’t have order by clause of-course it has distinct.
SELECT Max(Sal) FROM emp
WHERE Sal IN
(SELECT distinct TOP 3 Sal FROM emp )
and the query in the Main article is yielding correct result.
hi
this is the query to retrieve n th max salary record details
SELECT * FROM
(
SELECT DENSE_RANK() OVER(ORDER BY [Sal] DESC)AS RowId,*
FROM [dbo].[Emp]
) AS e1
WHERE e1.RowId = 4
i have three departments in my table viz cse,ec , it now i have to retrieve name of three persons who are getting maximum salary in their respective department
suppose my table is like this
name salary department
ashish 15000 cse
suresh 14000 it
mahesh 12000 it
ramesh 9000 cse
abhor 10000 ec
adhir 8000 ec
ankit 16000 cse
and i want result as
name salary department
ankit 16000 cse
suresh 14000 it
abhor 10000 ec
can i achieve this in a single query please help me out
select name,salary,department from
(
select *, row_number() over (partition by department order by name) as sno
) as t
where sn=1
we can also find the nth salary as……..
select * from TableName E where n-1=(select COUNT(distinct SAL) from TableName where SAL>E.SAL)
u gues are excellent.. all these were my interview questions..!and i gt answers!!
Hi friend This will also helpful.
Here employeemaster is table nane
gross is column name
select top(1) gross from employeemaster where gross not in(select distinct top (4) gross from employeemaster order by gross desc) order by gross desc
Thank u…..
Regards,
Rajani.k
Hiiiiiii,
What is the main diff b/w Drop,Delete & Truncate?
drop is to drop a table like drop table tablename we can aso drop a store procedure,like drop proc pocname,.
delete is to delete the record.like:delete from tablename where id=1,
truncate is to completly eradicate all the records.
hope this will help u.
I think this will be the exact query: (n > 1)
select * from products where unitprice=(select min(tmp.unitprice) from (select top n unitprice from products order by unitprice desc) as tmp);
e.g. (Fifth highest) :-
select * from products where unitprice=(select min(tmp.unitprice) from (select top 5 unitprice from products order by unitprice desc) as tmp);
select max(sal) from emp a where n=(select count(sal) from
emp b where b.sal >=a.sal)
Thanks
1st,2nd,….Nth (the following query maximum of salary using row_number() function)
SELECT A.SALARY FROM(SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROWID,* FROM EMP) A WHERE A.ROWID=N
IF N=1 THEN 1ST MAXIMUM
IF N=2 THEN 2ND MAXIMUM
.
.
.
.
.
.
How can I get the nth highest salary using the join?
hello sir…
thank you for guidence…
it is very help full to us
i have a one table marks1,Marks2,Marks3 as coulmns,,
now i want to get least Marks form entire table, not at in particular row/Coulmn
how can i get..
reply me sir…
hi friends,
i have a one query that can we import oracle’s table in sql server 2005
Hi Rajani,
I have gone through the question that you have given. I really found this interesting and here is the solution for you.
Select top 1 *,
Case When marks1< Marks2 And marks1< Marks3 Then marks1
When Marks2 < marks1 And Marks2< Marks3 Then Marks2
Else Marks3
End As MinMark
From TableNameHere
hello to all
Select query without using “Like” operator . For eg select employee name start with ‘j’ and city is ‘Noida’ in company_name
Hello Rama,
I had gone through the question that you have given and found this solution for your question:
SELECT SUBSTRING(emailID,NULLIF(PATINDEX(‘%s%’,emailID),0),3) AS EMAIL FROM CustomerDetails
This will definitely solve your problem.
Thanks & Regards
Sisir Patro
how to get highest second salary without using max and top………………….
plz give me idea and query
I need to find the 2 highest salaries of each employee. How would I do something like that?
select empid,salary from
(
select empid,salary,row_number() over (partition by empid order by salary desc) as sno from table
) as t
where sno<=2