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 (http://blog.SQLAuthority.com), Pravin Phatangare
Hi Pinal,
myself Dharmendra, and am a Regular reader of ‘SQlAuthority’.
its a Good one Article about finding nth Higest Salary..
if am not wrong we can also do the same thing by using aggreate function like Min or Max..
Like if i want to know 4th Highest Salary then i can
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)
Regards
Dharmendra k Dixit
thanx dixit sir
hi Dharmendhra, this is sekhar. you r absolutely correct. It works.
Thank u very much Dixit
Thanks dixit sir for co-operation.
Thank you Dharmendra Because it is very useful for any reader can read this one …
I really Satiesfied to do this
Dude
Dude if we have same salaries like 3000 for two employees then your query count the same salary twice. So we cannot use this query
Try Distinct in the subquery.
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT DISTINCT TOP 4 Sal FROM TableName ORDER BY Sal DESC)
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL(SELECT MAX(SAL) FROM EMP)
This is same as
SELECT MAX(SAL) AS SAL FROM EMP
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL(SELECT MAX(SAL) FROM EMP) its not working make it correct
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL NOT IN
(SELECT MAX(SAL) FROM EMP) its not working make it correct
Find lot of methods at http://beyondrelational.com/modules/2/blogs/70/posts/10790/find-nth-maximum-value.aspx
WOWWWWWWWWWWWWWWW DIXIT YOU ARE RITE..
IT WORKED FINE……… THANKS
nothing like wowwwwww
–CREATE TABLE EMPLOYEE( EMPLID NUMBER,
EFFDT DATE)
– insert into EMPLOYEE
— values(99, to_date(’1998/03/31:12:01:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’));
SELECT * FROM EMPLOYEE
SELECT * FROM EMPLOYEE E WHERE 1= ( SELECT COUNT(DISTINCT E1.EFFDT) FROM EMPLOYEE E1
WHERE E1.emplid = E.emplid AND E1.effdt >= E.effdt
AND E1.EFFDT < = SYSDATE
)
Hi Dixit,
Your query is working fine. But I think this is not used for same salaried employees. Distinction should be mentioned there.
Thanks.
How about this?
SELECT * FROM EMPLOYEE WHERE ESAL = (
SELECT ESAL FROM (
SELECT DISTINCT DENSE_RANK() OVER(ORDER BY ESAL DESC) AS [RANK], ESAL FROM EMPLOYEE )AS A WHERE [RANK] = 3);
When I compare to your query, mine runs 4 % faster but it is on very small dataset of 5 employees. Don’t know how it will react to bigger datasets.
You query is ran as follows,
SELECT * FROM EMPLOYEE WHERE ESAL IN
(
SELECT MIN(ESAL) FROM EMPLOYEE WHERE ESAL IN
(SELECT DISTINCT TOP 3 ESAL FROM EMPLOYEE ORDER BY ESAL DESC)
)
How about this,
SELECT * FROM EMPLOYEE WHERE ESAL = (
SELECT ESAL FROM (
SELECT DISTINCT DENSE_RANK() OVER(ORDER BY ESAL DESC) AS [RANK], ESAL FROM EMPLOYEE )AS A WHERE [RANK] = 3);
My above query works 4% faster than yours on small dataset of 5 employees
Ya it is easily understand thanks
Hi Dixit, You are not right. because it could be possibility that some salary also duplicate ..so you should use distinct keword as well.
Works like a Charm!!
I think this question is one of the highest hits in interviews…
Thanks.
how can i get second hight salary or 3 rd hight salary
here
iam giving example
select top 1 quantity from(SELECT TOP n-1 quantity FROM (SELECT DISTINCT TOP n quantity FROM productorder ORDER BY quantity DESC) as A ORDER BY quantity desc )as B order by quantity
n->represt top nth records
n-1 is the which highest salary you want
@ Dharmendar
Bro.. your code is quite long and might be resource consuming because ,
1. your are selecting the minimum from a whole bunch of values.
2. You need to write Distinct in the query only then you will give the correct answer.
Like your code should be :
SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC)
Yes Br. u r correct.
u people are excellent
HELLO !
THIS QUESTION IS DIFFICULT .
BUT I TRY THE ANSWER
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY DESC; ——–> (THIS IS USED FOR RETRIEVE THE MAXIMUM TOP 5 PAID EMPLOYEES )
OR
TO RETRIEVE THE MIN VALUE USE THIS
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY;
THANK YOU.
its not working
so post right ans
Nice.
It really helps a lot.
Thanks.
To Imran..
Yes Bro ..Thanks for ur Suggestion..
it might be resource Consuming..
but i think so, its an alternative for finding nth Highest Salary..
looking forward ur openion..
Regards
Dharmendra k Dixit
hi there guys i need a query but i can´t get it
here i go the table has department_id, salary, employee_id
how can i increase the salary of all employees of a department in the min salary paid in that department
as example department 1 the min sal paid is 1000, i have to paid now to each employee his salary +1000
hi
u can try this….
UPDATE emp
SET sal = sal +
(SELECT MIN(sal)
FROM emp E2
WHERE e2.deptid = emp.deptid
GROUP BY deptid)
I think following query will give second highest salary. You can get nth highest by replacing 2 with n.
select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
Hi Chirag,
Can u plz let me know how the Query works,
Cheers,
Sunil
Yours answer is the best
select top 1 * from (select top 2 * from tabname order by sal desc )tabname order by sal asc
super…
very nice…
I think this query won’t if more than one employee have the same salary.
It would not work on following table.
e1 100
e2 200
e3 300
e4 300
Hi,
I am Praveen. I am visiting this site for first time. The above query is really good and generic. We can use this also to get nth lowest salary by just changing the order from DESC to ASC.
haiiiiiiiii sir it worked…..
hi
i hv tried with this query but i am not able to get the exat answer
i tried with this query it works fine
===========================
put any digit instead of 5 u can get exat result
select distinct a.salary from Salary a where 5=( select count(distinct b.salary) from Salary b where a.salary<=b.salary)
==================================
ok friends tell me which query wil be faster
this query or using top n query
u can mail me at
dolagobinda.sahoo@gmail.com for my feed back
For SQL Server 2005 you have a Windowed function ROW_NUMBER, which you can use as follows:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (order by EMP.SAL DESC) AS RANK FROM EMP) v where RANK = 10;
Is this query work if i am having some employee with the same salary?
Why dont you try it and check?
In continuation to my above comment, I agree that this solution might not give exact result while SAL has duplicate values, but again one can play with the query a little bit. and get desired results.
I required sql query for select top 2 highest paid employee from each department
SELECT *
FROM emp e1
WHERE (1 =
(SELECT COUNT(DISTINCT sal)
FROM emp
WHERE e1.sal <= sal AND e1.deptid = deptid))
UNION
SELECT *
FROM emp e1
WHERE (2 =
(SELECT COUNT(DISTINCT sal)
FROM emp
WHERE e1.sal <= sal AND e1.deptid = deptid))
Hi friends,
select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
This query is ‘selecting the n th highest salary’. This query is correct.
I want know how to run this query. please explain this sql query.
this query is working
but i want to display all colunms available on emp table inforomation who r getting 3rd highest salary
Select * from table
where salary in
(
your_query
)
SELECT MIN(sal) FROM tablename
WHERE sal IN
(SELECT TOP 3 sal FROM tablename ORDER BY sal desc)
hai ,
iam just asked that i want to know the maximum salary and minum salary along with the employee details
select * from tablename where sal =(SELECT MIN(sal) FROM tablename
WHERE sal IN
(SELECT TOP 3 sal FROM tablename ORDER BY sal desc))
Hi Prasad,
Hope this will work for you
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MIN(b.Salary)
FROM #T1 AS b)
UNION
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MAX(b.Salary)
FROM #T1 AS b)
ORDER BY a.Salary
DROP TABLE #T1
GO
The above query will also give you List of Employees who has Min Salary and List of employees having Max Highest Salary
Vamshi
For getting list of employees with Nth Highest Salary, Here is another approach, Probably this would be similar to one of the above queries
DECLARE @n INT — Variable for accepting N
SELECT @n = 2 — Get the Second Highest Salary
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
SELECT a.ID1, a.[Name], a.Salary
FROM #T1 AS a
WHERE (@n-1) = (
SELECT COUNT(DISTINCT(b.Salary))
FROM #T1 AS b
WHERE b.Salary > a.Salary)
DROP TABLE #T1
GO
Vamshi
This can also be done using DENSE_RANK() function of SQL Server 2005.
DECLARE @n INT — Variable for accepting N
SELECT @n = 2 — Get the Second Highest Salary
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
WITH T1 AS
(SELECT (DENSE_RANK() OVER (ORDER BY Salary DESC)) AS RNum, * FROM #T1)
SELECT a.ID1, a.[Name], a.Salary
FROM T1 AS a
WHERE a.RNum = (@n)
Vamshi
Hi guys,
My requirement is like this.
I need four data belonging to four latest audit dates for every business.
I used something like…
SELECT TotalUnitCount FROM @TotalUnitsInLot SecondLatestTotalUnits
WHERE (2-1) =
(SELECT COUNT(DISTINCT(LatestTotalUnits.AuditDate))
FROM @TotalUnitsInLot LatestTotalUnits
WHERE LatestTotalUnits.AuditDate > SecondLatestTotalUnits.AuditDate
AND LatestTotalUnits.BusinessId = SecondLatestTotalUnits.BusinessId
AND LatestTotalUnits.BusinessId = TotalUnits.BusinessId
GROUP BY BusinessId
)
This gives me the results I want, but this is really slow. The problem with
SELECT TOP 1 TotalUnitCount
FROM (
SELECT DISTINCT TOP 4 TotalUnitCount
FROM TotalUnitsInLot
ORDER BY TotalUnitCount DESC) a
ORDER BY TotalUnitCount
approach is, if there are only 3 audits for a business, this query still gets the data from 3rd audit and gives me those results. If there is no data for the 4th audit I want to show TotalUnitCount = 0 for the fourth audit. How do I do that ?
Give the desired Input and Output clearly in the form of a table. then it would be easy for anyone to answer your question.
Thanks…
my requirement is to get all the emp names ,their respective manager names in a single query..
like
empno emp_name Emp_mgr
101 xyz 105
102 abc 104
103 red 101
104 kfc 105
105 desa 118
here i need emp_name and his manager name..like
emp mgr
xyz desa
abc kfc
Hi Pavan
Below is the answer to your problem
Select a.emp_name,b.emp_name as manager from empman a, empman b where a.emp_mgr = b.empno
I hope this solves your problem
ashish how to find out Avg sal of all employees means like
manager1 contains 4 employees
manager2 contains 10 employes
manager 3 contains 6 emps…ok
my question is under manager 1 avg sal of 4 emps and mgr 2 avg sal of emp’s….
Hi,
One question in this query – what if there are multiple employees who has the nth highest salary? How can we get all the employees with the nth highest salary?
Thanks,
Kaushik.
I have about 50000 records inn a table sorted by MODEL_ID
I want to select the last 200 highest MODEL_ID in the table.
How would I do that?
BUSHIKSHIK
select min(sal) from (select top 200 sal from table order by sal desc) as a
Pls Help Me
Want SQL Query to retrieve records from two tables but the records which are same in both tables happen once.
hey raman contact me for other queries.
Hi All,
How to find a highest salary in employee table with emplooyee name.
plz help me on this
There are many methods availble
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
hi kumari
this is anurag
use sub query
select name,sal from emp where sal=(select max(sal) from emp);
select emp_name,salary from salary_tab where salary=(select max(salary) from salary_tab)
Hi,
I tried with this query but i am not satisfied.
could u please explain in detail.
Hi
This query gives complete details of employee who are getiting max/min salary
select * from emp where salary=(select max(salary) from emp);
select * from emp where salary=(select min(salary) from emp);
(note: here table name emp,salary is coumn name)
thank u …..
This query gives highest salary for employee and employee name.
select empsalry,empname
from empsalr
where empsalry = (select max(empsalry)
from empsalr)
select empsalry,EMPNAME
from empsalr
where empsalry = (select max(empsalry)
from empsalr)
Please Help
I am trying to calculate the Median of DATEDIFFERENCE for each Episode. I have succeeded at obtaining the Difference in dates but am unable to extract the nthsmallest by episode.
My Query right now is
SELECT TOP 1 PERCENT DATEDIFF(dd, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE]) AS DIFFERENCE,
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE],
TABLE2.[Act START DATE] AS [NEXT Session], OPENCLOSE.OPENID
FROM dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE INNER JOIN
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE TABLE2 ON
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE] = OPENCLOSE.OPENDATE AND
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO = OPENCLOSE.CASENO AND TABLE2.[Act START DATE] > OPENCLOSE.OPENDATE
GROUP BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE], dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO,
OPENCLOSE.OPENID
ORDER BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE]
Sorry if I confuse anyone I am new with SQL I handle Crystal Reports XI and minimal SQL. By the way I am running SQL Enterprise 2000.
Thank You
I want to know query to get highest paid salary for each department from Dept(Dept type,Id) and Employee table(Name,Emp no,..).
Aslo want to know query to list all employee from sales Dept from Employee and Dept Table
Question :
select the name of Manager who has max number of employees .
(Only the name of manager nothing else)
solution : ( I am posting my solution and hoping if someone has better one please respond ).
create table #mgr ( id int not null, name varchar(10) )
create table #emp ( id int not null, mgrID int, name varchar(10))
(note: mgrID is foreign key from #mgr )
insert into #mgr values (1, ‘mgr1′)
insert into #mgr values (2, ‘mgr2′)
insert into #mgr values (3, ‘mgr3′)
insert into #mgr values (4, ‘mgr4′)
insert into #emp values (1, 1, ‘emp1′)
insert into #emp values (2, 1, ‘emp2′)
insert into #emp values (3, 1, ‘emp3′)
insert into #emp values (4, 1, ‘emp4′)
insert into #emp values (5, 1, ‘emp5′)
insert into #emp values (6, 1, ‘emp6′)
insert into #emp values (7, 1, ‘emp7′)
insert into #emp values (8, 2, ‘emp8′)
insert into #emp values (9, 2, ‘emp9′)
insert into #emp values (10, 2, ‘emp10′)
insert into #emp values (11, 2, ‘emp11′)
insert into #emp values (12, 2, ‘emp12′)
insert into #emp values (13, 2, ‘emp13′)
insert into #emp values (14, 3, ‘emp14′)
insert into #emp values (15, 3, ‘emp15′)
insert into #emp values (16, 3, ‘emp16′)
insert into #emp values (17, 3, ‘emp17′)
insert into #emp values (18, 4, ‘emp18′)
insert into #emp values (19, 4, ‘emp19′)
insert into #emp values (20, 4, ‘emp20′)
select m.name from #mgr m
join
(
select m.id, count(m.id) as cnt
From #mgr m
join #emp e
on m.id = e.mgrID
group by m.id
) x
on m.id = x.id
Where x.cnt =
(
select max(cnt) from
( select m.id, count(m.id) as cnt
From #mgr m
join #emp e
on m.id = e.mgrID
group by m.id
) y
)
how can we get the highest,2nd highest and 3rd highest salary from the employee table along with the employee names in sql
Q)How can we get the highest,2nd highest and 3rd highest salary from the employee table along with the employee names in sql ?
Ans:-
select Emp_Sal,Emp_Name from employee where Emp_Sal in (select top 3 Emp_Sal from employee order by Emp_Sal desc);
In the salary senario , you must also take care that salaries can be duplicate in a table , the easiest way to get nth heighest salary is
select top 1 salary from
(select top n salary from employee e1 group by e1.salary order by e1.salary desc) e2
order by salary
Its real simple. Suppose your employees are in a table called by the same name, and the salaries in a column named salaries). Lets See te case in which we want the 3 greatest salaries. Then you Query:
SELECT employees.salary
FROM employees
ORDER BY employees.salary desc
LIMIT 3;
If you want the biggest 5 salaries, you put LIMIT 5, and so on….
@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.
http://msdn.microsoft.com/en-us/library/ms177634.aspx
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.
‘,sp_tables –’
hi this is srinu,
i have visited ur site multiple times,
really ur publishing valuable information to beginners like me.
in this query u used a
SELECT TOP 1 Sal
FROM (
SELECT DISTINCT TOP 2 Sal
FROM Emp
ORDER BY Sal DESC) a
ORDER BY Sal
why did u use that a variable can u explain me..
thanks in advance
regards
Srinu Ganaparthi
@Srinu,
Which variable you are talking about, I dont see any variable.
Please post your complete question.
~IM.
Hi Sinu,
To consider that whole query as single table we need to use that variable..even you can use whatever the variable you like not only a..u can use b..whatever u like
hope it will give u good idea on that.
@ Srinu
No need to confuse with the ‘ a ‘ . Its just a alias name given for the table particularly in the case of sub queries. Be aware that we written a sub query to achieve the result.
If u didn’t specify the name in SQL Server 2000/2005 , it wont give any effect to the result but just observe that when u execute the query system will automatically attach table name_1 alias name to the table.
I hope u got somewhat clear idea.
Regards,
Raj
@ Srinu ,
Just check this query . It will give 4th highest salary from the list of employee salaries.
SELECT MIN(empsal) AS Empsalary
FROM emp
WHERE (empsal IN
(SELECT DISTINCT TOP (4) empsal
FROM emp AS emp_1
ORDER BY empsal DESC))
notice that emp_1 is created while executing the query. Its automatically generated one.
@ All
Thanks guys … for valuable posts … keep posting … its a good sign for us … we can share and solve others problems too….
regards,
Raj
i want to retrive nth max sal from emp……….this query gvn eroor please solve it and tell me
SQL> SELECT TOP 1 sal
2 FROM (
3 SELECT DISTINCT TOP 6 sal
4 FROM emp
5 ORDER BY sal DESC) a
6 ORDER BY sal;
SELECT TOP 1 sal
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT sal
2 FROM (
3 SELECT DISTINCT TOP 6 sal
4 FROM emp
5 ORDER BY sal DESC) a
6 ORDER BY sal;
SELECT DISTINCT TOP 6 sal
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
The way to find nth ranking, is with RANK().
SELECT sal FROM
(SELECT sal, RANK(ORDER BY sal DESC) rn)
WHERE rn = rank-you-want
This will report all people at that level. If, however, you want one and only one, ROW_NUMBER():
SELECT sal FROM
(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn)
WHERE rn = rank-you-want
Please explain the following query as in how it is interperated.
SELECT *
FROM emp A
WHERE (n-1) = (
SELECT COUNT(DISTINCT(B.salary))
FROM emp B
WHERE B.salary > A.salary)
Hi, Pinal.
It is a great article on finding the nth highest value in a column.
I have a dummy table emp(emp_name,deptname,salary)
and i want to find out the second highest salary department wise.
would you please help me to sort it out ?
anyone’s help will be greatly appreciated..
Thanks and regards
Deba
@debadutta..
u can work on this…
select max(e1.sal), e1.deptno from emp e1
where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal)
group by deptno;
u might get ur solution….i supose…just work on this
Thanks
so good answer
superb…
thankx..
very goood.
@debadutta..
u can work on this…
select max(e1.sal), e1.deptno from emp e1
where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal)
group by deptno;
u might get ur solution….i supose…just work on this
select max(e1.sal), e1.deptno from emp e1
where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal)
group by deptno;
can u explain this query how its work
explain where condiation also ????
@Mookkandi @sharad
Here’s an explanation of how the following query works:
For each row, this query makes a cartesian product with other rows of the table. Then according to the inner query syntax, it compares the salaries. Then, the number comparison in the first outer query makes sure that only those records are picked which satisfy the criteria. E.g.
Let’s say u got 3 records, with salaries 10, 20 and 30. So, the cartesian product is something like,
1) For row with sal 10 –> 10-10, 10-20, 10-30
2) For row with sal 20 –> 20-10, 20-20, 20-30
3) For row with sal 30 –> 30-10, 30-20, 30-30
Now apply the condition, e1.sal 10-10, 10-20, 10-30 (3)
2) For row with sal 20 –> 20-10, 20-20, 20-30 (2)
3) For row with sal 30 –> 30-10, 30-20, 30-30 (1)
Now the outer condition, 2<= (the count arrived at above). So only 2) and 3) get selected. Hence, it shows salaries according to outer query for these two i.e. 20 and 30.
PS: This is inefficient and should be avoided. Prefer rank() in Oracle
Hope it helps.
This query has good execution plan among all the queries explained here .
Select *
from (Select *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Ranks from #T1) a
WHERE Ranks = N
Thanks,
-Amol.
Hey Pinal,
Good Blog. But just wanted to let you know that there should be a very minute change required in this query:
USE AdventureWorks;
GO
SELECT TOP 1 Rate
FROM (
SELECT DISTINCT TOP 4 Rate
FROM HumanResources.EmployeePayHistory
ORDER BY Rate DESC) A
ORDER BY Rate DESC –Desc should be there in order to get top record
GO
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
enter n value.if u want 3rd max salary place n value 2.
Thank guy…….
i have question in oracle
how can i find the second highest manager who has second highest number of employees reporting to him.
say for eg:
if mgr 789 has 3 employees reporting to him
and mgr 889 has 5 employees reporting to him and
mgr 779 has 2 employees reporting to him
i need to get mgr 789 as my answer as he has the second highest employees reporting to him
i need to find out this using sql query.
pls help
i have a question in sql,any one please help me to get out from here.
Q.Table name employee in which we have 200 emp list.i want to display the name of the employee who have highest salary from 200 employee table data.
Please help me to get the answer.
Hi harish,
You can use this Query:
Select * from table where sal = (select max(sal) from table)
Seriously I want to hug whoever posted this answer originally. THREE DAYS of trying to figure out this code and now it WORKS !!!
THANK YOU THANK YOU THANK YOU !
SELECT TOP 1 Invoicetestdate1.[TestDate] AS Expr1
FROM (SELECT DISTINCT TOP 2 Invoicetestdate1.[TestDate]
FROM Invoicetestdate1
GROUP BY Invoicetestdate1.[TestDate]
ORDER BY Invoicetestdate1.[TestDate] DESC) AS a
ORDER BY Invoicetestdate1.[TestDate];
invoicetestdate1 is another query based on a filter generated by a form. But this solution works GREAT !
thanks anurag.it solved my problem.
here is another q i want to share.
q.how to do replication in sql server2005 with examle.
waiting…..
SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS ‘Salaries’
FROM #Employees
) emp
WHERE Salaries = n
n may be 2,3….
pls. help how to increase the current salary by 15%?using sql codes….tnx
update empregistration set salary= salary * 1.15….I hope this will help you….enjoy…..
That is definetely something I would like to know too! :D
But seriously, just add it with 1.15 like: SELECT Salary * 1.15 as RaisedSalary
@rona
UPDATE employee SET Salary = Salary * 1.15;
select * from emp e1 where 2=(select count(*) from
emp e2 where e2.id > e1.id);
id values – 1,2,3,4,5
Here i am getting third highest value
can any one explain me this query. here what is the use of value 2. and how we are getting third highest value i.e id 3.
Regards,
Rock..
i dont understand what your query displays
give brief explanation
I want to display every nth records using the select query in sql server . How can i achieve the same.
Hello Sunil,
Use the ROW_NUMBER fucntion to get a identity value for each row and then get the Nth row using modulo operator.
Regards,
Pinal Dave
thanx dear u made my day greate work … you solved my problem!!!!!
select * from(
select dense_rank() over (order by id)as rownum,* from tbl1
)temp where rownum=2
Hi…
How to get the highest rank holders list Without using subquery…
For example,
S1 80
s2 80
s3 79
s4 78
s5 78
s6 60
s7 70
I need to get Top 3 rankers from the list
Tht is
s1
s2
s3
s4
s5
How to get it without using subquery?
Thnks in Advance……
Hello Rocky,
Using DENSE_RANK() function in CTE you can get a raking value and retreive only the records where this ranking is less than 4.
For details see the following page:
http://msdn.microsoft.com/en-us/library/ms189798.aspx
Regards,
Pinal Dave
Instead of this you should use.
select Income from (
select Rank() over(order by Income desc) topRecord, Income
from (select distinct Income from tblName) tbl ) tbl2
where topRecord = 2
I am rahul Upadhyay This can be done without using TOP
SELECT *
FROM ( SELECT ROWNUM AS rank, Emp_id, Emp_salary
FROM employees
ORDER BY Emp_salary
)
WHERE rank=3 ;
i want to display 5 th highest salary from emp
complete row information(all columns of emp table who are getting 5 th highest salary)
select top 1 * from
(
select top 5 * from emp order by salary desc
) as t
order by salary
or
select * from
(
select row_number() over (order by salary desc) as sno,* from table)
as t
where sno=5
ROWNUM is for ORACLE
You need to use row_number() over (order by salary desc) in SQL Server
what is ‘a’ in the query
plz tell me.
a is alias.
alias name…….
Thank you Priyanka. I also had the same doubt.
a very simple way to get nth highest .. . i was using a complicated one :)
if you want to display highest salary from emp table
(display all emp table columns)
query is
select * from emp where salary=(select max(salary) from emp);
here sub query is used
above query displays complete information available on emp table who are getting highest salary
Other method is
select top 1 * from emp order by salary desc
–First Method
select top 1 salary from (
select top 6 salary from employee group by salary order by salary desc) a order by salary
go
–Second method
with nthsalary as
(
select salary,row_number() over(order by salary desc) as row from employee group by salary
)
select salary from nthsalary where row=6
GO
–Thired method
select distinct salary from employee a where 6=(select count(distinct salary)
from employee b where a.salary<=b.salary)
Top 3 Salaried Employee from Every Departments
create table #empdet(
empno int identity(1,1),
empname varchar(50),
sal numeric(18,2),
dep varchar(20)
)
go
insert into #empdet(empname, sal, dep)
select ‘A_123′,12000,’BANK’
Union
select ‘A_234′,5000,’BANK’
Union
select ‘A_345′,10000,’BANK’
Union
select ‘A_456′,25000,’BANK’
Union
select ‘A_567′,8000,’BANK’
Union
select ‘B_123′,25000,’PROG’
Union
select ‘B_234′,27000,’PROG’
Union
select ‘B_345′,23000,’PROG’
Union
select ‘B_456′,13000,’PROG’
Union
select ‘B_567′,50000,’PROG’
Union
select ‘C_123′,11000,’TEST’
Union
select ‘C_234′,9000,’TEST’
Union
select ‘C_345′,22000,’TEST’
Union
select ‘C_456′,30000,’TEST’
Union
select ‘C_567′,8000,’TEST’
Select * from #empdet
Select * from #empdet a where empno in (select top 3 empno from #empdet where dep=a.dep order by sal desc)
There are many methods availble to find this
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
hey Iam dinesh
I want to Add 3000 salary of tose employee where salary>20000
It is as simple as
update table
set salary=salary+3000
where salary>20000
select top 1 salary from(select distinct top 5 salary from student11 order by salary desc) a order by salary
This is not working for to to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table
Suppose table structure is as follows
EmpID EmpName Salary
1 EMP1 2000
2 EMP2 1000
3 EMP3 5000
4 EMP4 4000
5 EMP5 10000
6 EMP6 250
7 EMP7 100
8 EMP8 2100
Have you tried these methods?
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
Thanks Pinal,
Your post really helped for fetching nth highest height in SQL Server 2000. I was able to do it in SQL Server 2005 using rowNumber, but was struggling a bit in 2000.
Thanks again !!
Sharad
Refer this for more informations
Some methods would work in all the versions
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
This is one of the best sites which is not only informative but also exceptionally driven with intent to share knowledge.
Thanks Pinal .
Thanks and Regards
Jaiwanth
e.g. to find out 3rd hieghest salary from table abc.
Select top 1 salary from(select distinct top 3 (salary) from abc order by salary desc)a order by salary asc;
Hi Can anyone tell me what is the function of a in the below query.
SELECT TOP 1 salary
FROM (SELECT TOP 1 salary
FROM employees
ORDER BY salary DESC) a ——–> ?
ORDER BY salary
Query is superb. But i am not understood wat is the use of “a”
i am expecting a quick reply.. Thq.
Hey Pinal, thank you for your blog….
a is the alias name for the derived table
The query
SELECT TOP 1 salary
FROM employees
ORDER BY salary DESC
acts as a derived table
hiii….
plz tell me the sql query for finding that in which departmnet maximum no. of employees are there in employee table of oracle?
This site is for MS SQL Server. For ORALCE questions post at http://www.orafaq.com
plzz answer this question:
in which department employees are getting salary > 1,00,000 lakh
Post table structures, sample data and expected result
plz arrange this in ascending order:–
abc116
abc1115
abc12
abc11113
answer should be:
abc12
abc116
abc1115
abc11113 plz reply me soon..
Refer this post. This will help you. Choose the point that works for you
http://beyondrelational.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx
forget the book
Empsal table data
700
500
100
900
400
200
600
750
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salaryselect distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
[email addressed removed](amit is back)
Empsal table data
700
500
100
900
400
200
600
750
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salaryselect distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
query to find second highest salary is
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));
Output=750
query to find the third highest salary is
forget bookish logic
query to find third highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
[email addressed removed](amy is back)
Run query then arg
thank u bro,
it is helpful to a lot
thanks®ards,
sivajee
Hi
If more than one employee getting the same salary means then we can’t get the correct result using Top
You can use TOP WITH TIES
Hello Sir,
I am great fan of your and fact is am new in sql so please help me that where ALIAS a IS USED IN THE QUERY, i know its a silly question.
Select * from
(
select 1 as n
) as t
Here t is the alias for table and n is the alias for column
Find Nth Highest Salary of Employee Without Subquery
Hello Sir,
I am great fan of your and fact is am new in sql so please help me
Find Nth Highest Salary of Employee Without Subquery
Refer this post to know more methods
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
thank u ,
Thanks®ards,
Brijesh Shah
will you query work if more then one emp share same salary .. i guess no
No. You need to handle it
Hi pinal ,
The Above queries works fine if we need only salary to display.
In case if we want all coloumns then we can try follwing query
select * from t1
where c2 =
(
select max(c2) from t1 where c2 not in
( select top (N-1) c2 from t1 order by c2 desc )
)
Where N is the Nth highest salary required.
The perfromance of this query is really gud as comapre other ones. I have tested this in SQL 2000 and 2005 with 13 lacs records and found that it works much faster then above solution.
This works for duplicate reocords too.
Hi pinal ,
The Above queries works fine if we need only salary to display.
In case if we want all coloumns then we can try follwing query
select * from Employee
where Salary =
(
select max(Salary) from Employee where Salary not in
( select top (N-1) Salary from Employee1 order by Salary desc )
)
Where N is the Nth highest salary required.
The perfromance of this query is really gud as comapre other ones. I have tested this in SQL 2000 and 2005 with 13 lacs records and found that it works much faster then above solution.
This works for duplicate reocords too.
Dear sir ,
I simply fire a simple query “Select top 3 netsal from salaryslip” which gives me 1st three records but i want top 3 highest salary records.
Kindly help me sir
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
how to find two highest salary from every department, your suggestion should be appreciated.
Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
Executed the above query and get the correct result but as performance wise its not correct ,Is there any other way of query to get the Nth Highest value.
Refer different methods suggested here
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
public DataTable retrieve()
{
SqlCommand cmdret=new SqlCommand(“SELECT TOP 1 salary FROM(SELECT DISTINCT top 2 salary FROM tb_connect ORDER BY salary )a ORDER BY salary desc “,constr);
SqlDataAdapter da=new SqlDataAdapter(cmdret);
DataSet dts=new DataSet();
try
{
da.Fill(dts,”tb_connect”);
return dts.Tables["tb_connect"];
}
catch
{
throw;
}
finally
{
constr.Close();
}
it will gives second lowest salary
highest 4 th salary from tb_connect table
SqlCommand cmdret=new SqlCommand
(“SELECT TOP 1 salary FROM
(SELECT DISTINCT top 4 salary FROM tb_connect ORDER BY salary desc )
a ORDER BY salary “,constr);
Hi,
I have 2 tables
Staff
——
PID (primary key)
Firstname
LastName
Country
Postal Code
City
Street
DateofBirth
Salary
TelephoneNo
——————-
PID (primary key)
TelephoneNo (also primary key)
My question is -> Give a list of telephone number of Susan Winter (Remark: don’t specify the internal PID!- this confused me )
Can anyone help solve this please. i am beginner to SQL.
This forum helped me a lot. Thanks every one who posted :-)
@Tash
Didn;t get that question, Is this what you are looking ?
Select TelephoneNo
From Staff A
join TelephoneNo B on A.PID = B.PID
Where A.FirstName +’ ‘+A.LastName = @Name
~IM.
———————————Try this———————————
select distinct(e.salary) from
(select salary,dense_rank() over(order by salary desc)R from EMP)e
where e.R=3
———————————Try this———————————
———————————For Nth max———————————
select distinct(e.salary) from
(select salary,dense_rank() over(order by salary desc)R from EMP)e
where e.R=N
Any answer this question?
In oracle “TOP” keyword is there or not. And it is working in SQL queries.
Advance thanks
sreenivas
TOP is not supported in Oralce. You need to make use of rownum column to limit the data
Great
Gud logic
hi Pinal,
i want to know how to find the second highest salary for each department from emp table. if we have table like this:
name dept_name salary
——– ————— ———
raj Account 15000
sam Account 20000
roy Admin 8500
paul Admin 9000
jack Admin 12000
ram IT 25000
sir
i want to top 10 highest salary from employee table ( for ex: 100 rows of employee table( top 10 salary ))
Refer this and use 10 in place of 5 http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
the very easy answer is ( in mysql )
select salary from employee order by salary desc limit 1
by- balwant bishnoi
mo. 9035732236
Sometime the Basic of SqlQuery get Confused me
I try your above query
by creating following db
dbo.Test
Create Table Test (Name Varchar(50), Salary Numeric(18,2))
Insert Into Test (Name,Salary) Values (‘A’, 100)
Insert Into Test (Name,Salary) Values (‘B’, 90)
Insert Into Test (Name,Salary) Values (‘C’, 80)
Insert Into Test (Name,Salary) Values (‘D’, 70)
Insert Into Test (Name,Salary) Values (‘E’, 60)
Insert Into Test (Name,Salary) Values (‘F’, 50)
Insert Into Test (Name,Salary) Values (‘G’, 40)
Insert Into Test (Name,Salary) Values (‘H’, 30)
Insert Into Test (Name,Salary) Values (‘I’, 20)
Now as Per your above query i fired the same in my ssms
i get the result
(6th Highest)
50
but the query seems to be confuse me
SELECT dISTINCT TOP 6
SALARY
FROM TEST
ORDER BY SALARY DESC
100.00
90.00
80.00
70.00
60.00
50.00
now apply your query method
SELECT TOP 1 SALARY FROM(
SELECT dISTINCT TOP 6
SALARY
FROM TEST
ORDER BY SALARY DESC
) A
ORDER BY SALARY
i get the result 50
but in subquery i get the following ans
100.00
90.00
80.00
70.00
60.00
50.00
then how can top 1 work because in subquery 100 is first na then why we get the result 50. Please help me
i have a doubt in sql.
we have a employee table and having fields empno, empname, and managername. i want to retrieve managername who has more than 50 employees working under him
pls anybody knows pls send me
Following is the simple query to get the 3rd highest salary.
select distinct min(sal) from (select sal from(select sal from employee order by sal desc)where rownum<=3)
Instead of 3, we can substitute n values.
rownum is for ORACLE. You need to use row_number() function as described in this post at point 6
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
@sai
Following query will retrieve the managername who has more than 50 employees working under him.
select managername from employee group by managername having count(*)>50;
this worked for me :
select top 5 Salary from (SELECT TOP 5 * FROM employeetable ORDER BY employeetable.salary DESC) a ORDER BY salary
thanks Pinal Dave for this!!!
hey sir i have a query here which worked but i don’t understand how….please help me
mysql> select * from emp a
where 2>(select count( distinct(sal))from emp where sal>a.sal and a.deptno=deptno )
order by deptno;
it is the query to find two highest salaries in every department
hi its very nice thanks
Hi Pinal,
It’s really clean and good code for finding nth number of highest salary.
Thanks
Ravi
There are many methods. Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
hiii sir i have a query to find nth salary from employee table
its working correctly
please explain me how it is working
thanks
select distinct salary
from employee e
where
4=(select count(distinct salary) from employee where e.salary<=salary);
please explain the working of this query
nitendra kumar
It just assigns the serial number based on the highest salary and picks up the 4th highest salary
hey i have a ques…
i have a table with studentdetails having columns id,name,technology.. and another table course having column name technology,count..
the 2nd table course do not have any values whenever a row is inserted in studentdetails the corresponding technology is inserted in course table n if it is already present its count shud be updated … i need to create trigger for this query,
Why do you need a trigger for this? Just use a select statement
select technology,count(*) as tech_count from studentdetails
group by technology
SELECT * FROM (SELECT sal FROM emp ORDER BY sal DESC LIMIT 3) emp ORDER BY sal ASC LIMIT 3;
Thnx himanshu.. compact querry with desired result
can we type in this following querry to find the second max salary from employee table
select max(salary-1) from employee;
how is this query different from above mentioned queries to find the second max salary from employee table??
please reply
thanks
Pramod
No, you cannot. Because if you use this syntax, you may get salary-1
3rd highest salary:
select max(sal) from emp
where sal < (select max(sal) from emp
where sal < (select max(sal) from emp))
santanu, this is ineffecient. What if you want to find 10th highestest? You need to repeat the code 10 times. Easier methods are described here
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
Hi,
My name is Bernard from Brooklyn New York. The following example I used a CTE along with a windowing/ranking function in an attempt to solve the problem.
Creat table sal
(ID INT IDENTITY PRIMARY KEY,
LNAME VARCHAR(25) NOT NULL,
FNAME VARCHAR(25) NOT NULL,
SAL INT NOT NULL
)
GO
–USE A ROW CONSTRUCTOR FUNCTION TO ADD THE DATA INTO THE TABLE
INSERT INTO SAL
(VALUES
(‘WILLIAMS’,'JOHN’,36000),
(‘STONE’,'BILLY’,45000),
(‘RAMSEY’,'PAULA’,75000),
(‘MURPHY’,'BERNARD’,100000));
WITH NARDO
AS
(SELECT EMPNO, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS ‘NTHSALARY’
FROM SAL
)
SELECT EMPNO, SAL, NTHSALARY
FROM NARDO
WHERE NTHSALARY = 2
Hi Bernard,
This is Dhirendra, in Sql server 2008 the insert statement is not working properly as per your query otherwise the concept of using Common Table Expression(CTE) is quite good. You can insert data in this manner too:
Create table sal
(ID INT IDENTITY PRIMARY KEY,
LNAME VARCHAR(25) NOT NULL,
FNAME VARCHAR(25) NOT NULL,
SAL INT NOT NULL
)
GO
INSERT INTO SAL(LNAME,FNAME,SAL)
select ‘WILLIAMS’,'JHON’,3600
union all
select ‘STONE’,'BILLY’,45000
union all
select ‘RAMSEY’,'PAULA’,75000
UNION ALL
SELECT ‘MURPHY’,'BERNARD’,10000
WITH NARDO
AS
(SELECT ID, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS NTHSALARY
FROM SAL)
SELECT ID, SAL, NTHSALARY
FROM NARDO WHERE NTHSALARY = 2
Thanks,
Dhirendra
I didn’t realize I made a slight boo-boo with respect to the insert statement. Sorry about that.
Display the 3rd highest salary. using this query we can find out Nth maximum value. in the 1st where clause put the number you want to display.
select salary from(select salary from(select salary from employee
order by salary desc)
where rownum<=3
order by salary)
where rownum=1;
its very simple to understand.
SELECT max(age) FROM Student
WHERE age < ( SELECT max(age) FROM Student
WHERE age < ( SELECT max(age) FROM Student
WHERE age < ( SELECT max(age) FROM Student )));
What does this query do? Finding thrid maximum? It will be slow for large tables. Refer alternate methods here http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
SecondHighest—
SELECT max(age)SecondHighestAge FROM e WHERE age < ( SELECT max(age) FROM e);
Hi,
You can do the same thing in good manner with CTE (Commom table expression)
with cte
as
(
select age,dense_rank()over(order by age desc) as row from e
)
select * from cte where row=2;
Thanks,
Dhirendra
How can I get the Nth highest salary in Sql server 2008
Refer these methods
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
The letter A appears after nested select statement. What does this do? Thanks.
It is a table alias for the subquery
I just need to know how to increase a salary by 10% using SQL? What command do I need to use…. Increase all employees’ salaries with the selected EEO-1 classification by 10%.
select empid,ename,esal,esal+(esal*10/100) as Increment_Salary from emp
For accuray use this
select empid,ename,esal,esal+(esal*10.0/100) as Increment_Salary from emp
For more informations refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx
This is simple
select salary+salary*0.10 from table
Hi Pinal,
I am Hitesh Shah (Software testing profile), I am new in this Blog , and I want simple query which I can understand and speak in interview.
How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table?
Thanks
Hitesh Shah
if uou are new to this blog then please go though all the post in this thread.
Hai,i thought it will help .you to find the any highest sal as per requirement.i face the question on interview.
Select * from tablename e1 where N=(select count(distinct (e2.sal))from tablename e2 where e2.sal>=e1.sal
ex-
select * from emp e1 where 3=(select count(distinct(e2.sal)) from emp e2 where e2.sal>=e1.sal)
this above query is used to find the 3 max salary of table
Hi guys.This is one of the interview question.
how to display emp salary and his manager salary using emp table.
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
.
.
. 2nd highest value ????????????????
select max(salary) as ‘second highest salary’ from tbemployee where salary not in
(
select top 2 (salary) from tbemployee order by salary desc
)
Respected EveryOne
how can i fetch tha salary between 5 to 10 highest salary from a table ???????
Looat at point 4 pagination here
http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx
Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,15000.00 UNION
SELECT 2,’KESAV’,52890.00 UNION
SELECT 3,’HANU’,24000.00 UNION
SELECT 4,’ARUN’,42200.00 UNION
SELECT 5,’KISHORE’,25000.00 UNION
SELECT 6,’VINOD’,32000.00 UNION
SELECT 7,’SENDIL’,22890.00 UNION
SELECT 8,’RAKESH’,26400.00 UNION
SELECT 9,’SURYA’,25200.00 UNION
SELECT 10,’SIVA’,28900.00
SELECT * FROM #EMP
SELECT * FROM #EMP ORDER BY SAL DESC
–TO GET ONLY TOP 5-10 HIGHEST SALARIES
SELECT TOP 5 SAL FROM #EMP WHERE ENO NOT IN
(SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC
–TO GET ONLY TOP 5-10 HIGHEST SALARIED EMPLOYEE DETAILS
SELECT TOP 5 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC
DROP TABLE #EMP
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RN,* FROM #EMP) AS T WHERE T.RN BETWEEN 6 and 10
–also you can use
“select max(salary) from (select distinct top 5 salary from Emp_Id order by salary desc) a “…why we have used ‘a’ after the subquery..will anyone pls tell me..
To make the further operations over the outer query (like) where etc it is use full
It is a alias name given to the derived table
Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,15000.00 UNION
SELECT 2,’KESAV’,52890.00 UNION
SELECT 3,’HANU’,24000.00 UNION
SELECT 4,’ARUN’,42200.00 UNION
SELECT 5,’KISHORE’,25000.00
SELECT * FROM #EMP
SELECT TOP 1 SAL FROM #EMP WHERE ENO NOT IN
(SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get only 2nd highest salary
SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details
DROP TABLE #EMP
how one can find the third highest salary ,with using subqueries?
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 2 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details
even using Row_Number you can do this
SELECT ENO,ENAME,SAL FROM
(SELECT Row_Number() OVER (ORDER BY SAL DESC) AS RN,*
FROM #EMP) E WHERE E.RN =3
– To get the details of 3nd highest salaried emploee details
Hi Purna,
Your answer will not work when there is multiple record with same sal
for example:
if there is records like:
ENO ENAME SAL
1 ABC 3000
2 XYZ 3000
3 PQR 2000
4 DEF 1000
This time it will give record 3 as answer
I have solution of it:
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
in this query instead of result write your tablename instead of maths write your column name instead of 3 write your number which you want
Thanks
Give your response is this helpful or not
Hi Mr.DK,
Absolutely correct. But I’ll update my query now to this
Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,3000.00 UNION
SELECT 2,’KESAV’,3000.00 UNION
SELECT 3,’HANU’,2000.00 UNION
SELECT 4,’ARUN’,2000.00 UNION
SELECT 5,’Sanood’,5000.00 UNION
SELECT 6,’Vijay’,1000.00
SELECT ENO,ENAME,SAL FROM
(SELECT Dense_Rank() OVER (ORDER BY SAL DESC) AS RN,* FROM #EMP) E WHERE E.RN =3
Even this will work perfectly then Rank() function when there is multiple values with same Sal then Rank will escape some values
result:
ENO ENAME SAL
3 HANU 2000
4 ARUN 2000
Hi your query is right giving correct i have another solution
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
in this query instead of result write your tablename instead of maths write your column name instead of 3 write your number which you want
Thanks
Thanks for your Queries. These r very use full. Really thanks to one and all.
Regards
Sudheer.
select top 1 * from ( select top 7 * from city order by city_id desc) as city_id order by city_id asc
Q1. SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a
ORDER BY salary
what does this ‘ a ‘ means here.
Q2. How to auto number the rows in a table in Oracle SQL*9 ?
Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?
Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?
Hint : u have a Employee(empid,empname,salary) table
Please HELP ASAP!!!
–
Thanks in advance!
Vijesh Jain
you can do it by below query as well
suppose i have to find out the fifth highest salary of an employee
then
select Salary from
(select *,DENSE_RANK() over (order by Salary desc) as ‘num’ from tblEmployee) t1
where num=5
it will return the fifth highest salary of an employee
display top 2 salary holder’s from emp table
Thanks………..Its…..such helpful…
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
select max(sal) from emp where sal<(select max(sal) from emp);
sir please tell me suppose election polloing application that different categories
and find out highest votes in one by one categories
select top 1 salary
from ( select distinct top 2 salary
from Salary order by salary desc)a order by salary what is meaning of a after from () a order by salary
SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC),
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
both the queries are not woking in oracle it gives the error FROM keyword not found where expected
(TableName=Student, ColumnName=Mark) :=>
select *from student where mark=(select mark from(select row_number() over (order by mark desc) as t,mark from student group by mark) as td where t=2)
@Pinal
Thanks for different way of writing query but how would you find 2nd highest salary of employees, if 2 or more employees having same salary which is second highest salary
Please let me know
Thanks,
Amol P
select MIN(STUDENT_ID) from TBL_STUDENT where STUDENT_ID in( select distinct top N student_id from TBL_STUDENT order by STUDENT_ID desc)
Hi all,
Help me to write an sql query ..
suppose i have 4 departments. I need max salary in department(1) and 2nd highest salary in department(2) and third highest in dept(3) and least sal in 4th dept.
my table is like this
name sal dept
A 1000 1
B 1600 1
C 950 1
D 1050 1
E 960 2
F 800 2
G 840 2
H 1010 2
I 999 3
J 970 3
K 1000 3
L 940 3
M 1200 4
N 1050 4
P 888 4
R 799 4
I WANT THE OUTPUT LIKE THIS:
NAME SAL DEPT
B 1600 1
E 960 2 — SECOND HIGHEST IN DEPT
J 970 3 — THIRD HIGHEST
R 799 4 — 4TH HIGHEST
Is it possible to do it in SQL or we have to go for PLSQL. Please reply..
thanks in advance….
SELECT name, salary from emp order by salary desc limit 0, 1
This is specific to MySQL. But this site is for SQL Server
select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=1;
Second Last Greater Number
SELECT GrandTotal FROM
( SELECT GrandTotal,ROW_NUMBER() OVER(ORDER BY GrandTotal desc) As RowNum
FROM def_ServiceProvider ) As A
WHERE A.RowNum IN (2,3)
[...] Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answered this question here. “How to find the Nth Highest Salary of Employee”. [...]