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
pinaldave
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
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)
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.
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
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,
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.
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;
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
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.
hai ,
iam just asked that i want to know the maximum salary and minum salary along with the employee details
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
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
Hi,
I tried with this query but i am not satisfied.
could u please explain in detail.
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.
@ 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.