#SQL
#GROUp BY CLAUSE
#Example

How to get max salary in each department?

Table :

id salary fname department
1 10000 prakash IT

2 12000 Ramesh IT

3 15000 Manish SYSTEM

4 7000 Suresh DEVELOPMENT

5 7500 Mukesh DEVELOPMENT

6 9500 Kamlesh SUPPORT

8 6000 Lalit SUPPORT

9 9000 Umesh IT

10 8000 Tapan SYSTEM

11 9000 Ila DEVELOPMENT

12 4500 Patan SYSTEM

13 18000 Yugank SUPPORT


Query :

SELECT MAX(salary) AS salary,department FROM table1 GROUP BY department

Result :

salary department
9000 DEVELOPMENT
12000 IT

18000 SUPPORT

15000 SYSTEM

Now if we want to display fname,id of that record, we simply add those columns name to query like :

Query :

SELECT id,fname,MAX(salary) AS salary,department FROM table1 GROUP BY department

Result :

id fname salary department
4 Suresh 9000 DEVELOPMENT
1 prakash 12000 IT

6 Kamlesh 18000 SUPPORT

3 Manish 15000 SYSTEM


Result data is contain wrong fname and id. Becase group by always fetch first row of group. So, we have to include join in to our query.

Query :

SELECT t1.* FROM table1 t1 INNER JOIN (SELECT MAX(salary) AS s,department FROM table1 GROUP BY department) AS t2 ON t2.s = t1.salary WHERE t1.department = t2.department

Result :

id fname salary department
2 Ramesh 12000 IT


3 Manish 15000 SYSTEM
11 Ila 9000 DEVELOPMENT
13 Yugank 18000 SUPPORT

Like · Comment ·