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
@Maximiliano Grass
LIMIT is not ANSI SQL keyword, not untill SQL Server 2005. I tried checking in SQL Server 2008, I could not see it online.
What I found on internet is LIMIT is a key word used in MYSQL. ( I mean only in MYSQL) no other RDBMS uses it.
Can you provide more info about this key word. Looks cool.
Thanks,
IM.
If two records are there in the table in 3rd position then how to calculate this???
thanks pinkal … really great work …
Hi Pinal Dave,
i use to read blog past one year.
#1
i have the doubt on the finding nth maxi salary.
every one has their own method even myself too.
but which one is good performance ?
i tried to get clear from execution plan but still struggling to find which one is? can you please ?
#2 how to find the execution performance of a query by looking it.
just is there any execution time consideration for operators like union , joins etc
for example: the operator precedence in languages
can anyone tell me what is the meaning of a in the following query and what is the use of it
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
@ Prem.
It Shows the Sixth Maximum Salary.
~ IM.
thanks imran,
that much i know but i want to know about the meaning and use of a in the second last line
@ Prem,
Its is nothing but just a subquery.
If subquery is followed by a FROM clause, SQL Server treat this sub query as a table.
And in your script, the name of this table (formed by subquery) is a.
~ IM.
can anyone tell me what is the meaning of a in the following query and what is the use of it
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
” a “–>wat it means
@ramu
It is aliasing the TABLE in the FROM clause.
See the help for the FROM clause for more details.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017
hi pinal sir,
select top 1
from
(select top 3 salary
from emp35
order by salary desc) a
order by salary
i am not getting result for this query in sqlserver2000 and that to giving error like…i amnt specifyinf attribute name at first line why bcoz i want entire record(i dont want to show single column)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘from’.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword ‘order’.
@ramu
The first SELECT does not specify a COLUMN:
select top 1
salary
from
(select top 3 salary
from emp35
order by salary desc) a
order by salary
hi brain…..thank u for responding but ………i want to get the first record………..how can i by using that query(which is already posted)
@ramu
The DESC should be supplied in the outer query too.
The first SELECT does not specify a COLUMN:
select top 1
salary
from
(select top 3 salary
from emp35
order by salary desc) a
order by salary desc
hi pinal,
\12353-435764.tif.tif in one column but iwant same thing in another which is existing column \12353-435764.pdf.
i want only .pdf extension only that to only one time
if i tis like this i done like
update tbl
set col2=replace(col1,’.tif’,’.pdf’)
\12353-435764.tif
but i dont know wen it is in \12353-435764.tif.tif
how to convert into
\12353-435764.tif.tif–>\12353-435764.pdf
i amnt able to do like this by using patterns
i want all columns details of 2 employees who is taking max(sal) from each department.pls gve me right answer
Hi Sam,
If you are using SQL SERVER 2005 and above, you can use ROW_NUMBER feature as:
;with cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowID,
*
FROM TmployeeTable
)
SELECT *
FROM cte
WHERE RowID = 1
This will give you list of all details of employee getting highest salary and department wise.
Let me know if it helps you.
Thanks,
Tejas Shah
(SQL Yoga)
thanks shah.
Hi friends ,
i am new to SQl.. so could u please help me out in finding the second highest and third highest sal .
And please explain me the query what it does.
example :
1> SELECT * from employee e1 WHERE 1=(SELECT COUNT(DISTINCT salary) from employee e2 WHERE e1.salary SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
what u mean by TOP1. Is it a column name in the table or to display in the out put.
please do explain me the querry.
Thanks in advance…………..
Regards,
Deepak
Some thing missed in the above querry. please do refer to this
1> SELECT * from employee e1 WHERE 1=(SELECT COUNT(DISTINCT salary) from employee e2 WHERE e1.salary SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
what u mean by TOP1 & TOP6. Is it a column name in the table or to display in the out put.
please do explain me the querry.