CODEWITHSUNDEEP
mysqlsql
Karthick V
Karthick V

Reputation: 1289

MySQl-getting salary of each dept

How can I get the top two salary of each department? Table looks like:

-------------------------
name     dept      sal
-------------------------
name1    CSE       100000
name2    CSE       200000
name3    ECE       200000
name4    EEE       400000
name5    CSE       700000
name6    ECE       600000

Upvotes: 0

Views: 678

Answers (6)

Karthick V
Karthick V

Reputation: 1289

 select profession as d,(select max(sal) 
 from emp where profession like d) as firstsal,(
 select sal from emp 
 where profession like d order by sal desc limit 1,1) as secondsal from emp 
 GROUP BY profession

Upvotes: 0

Code Spy
Code Spy

Reputation: 9954

For First Dept

SELECT dept
FROM  `Table` 
WHERE dept = 'CSE'
ORDER BY sal DESC 
LIMIT 0 , 2

For Second Dept

SELECT dept
FROM  `Table` 
WHERE dept = 'ECE' 
ORDER BY sal DESC 
LIMIT 0 , 2

Each Above Query will show you Top two results from each Dept.

UPDATE : 

SELECT dept, sal
FROM  `tbl_practice` 
WHERE dept =  'CSE'
OR dept =  'ECE'
ORDER BY sal DESC 
LIMIT 0 , 4

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 4601

Try this Query

select * from (
 SELECT  DISTINCT dept
        FROM    tableName )a
join tableName b
on a.dept >=b.dept
and 
a.dept <=b.dept
and sal >=(
select sal from tableName 
  where a.dept=b.dept
  order by sal limit 1,1
)

Upvotes: 0

somnath
somnath

Reputation: 1335

Dont think in a single SQL statement you will be able to get the data. If you want to achieve it using SQL, you will need to use a stored procedure that will loop thru all the depts and fetch the top 2 salary rows using limit and order by.

Upvotes: 0

ChrisW
ChrisW

Reputation: 5068

Use LIMIT in your statememt: http://dev.mysql.com/doc/refman/5.5/en/select.html

Upvotes: 0

John Woo
John Woo

Reputation: 263703

you need to use an aggregate function SUM and a GROUP BY clause.

SELECT dept, SUM(Sal) TotalSalary
FROM tableName
GROUP BY dept
Order By TotalSalary DESC
Limit 2

Upvotes: 0

Related Questions