Find miximum and minimum gross salary

Question

1.00/5 (2 votes)

See more:

How can find-out Department wise Every Designation maximum GS And Minimum GS with empid.

What I have tried:

select A.EmpID,A.EmpName,B.DeptName,A.DeptId,D.DesigName,A.DesigID, GS from tblEmp_Info AS A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID 
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where IsInactive=0 and A.DeptId=4
order by A.deptid,A.desigid,GS desc

this is my data base code, here is show all gs(gross salary) with maximum to minimum.but i want only Department wise every designation miximum and minimun GS with empid. pls help me ASAP.

Posted 8-Apr-17 21:01pm

Glorious007

Updated 9-Apr-17 2:25am

Add a Solution

2 solutions

Solution 1

Use aggregate function with group by clause on the selected columns on which u want to retrieve.

select B.DeptName,A.DeptId,D.DesigName,A.DesigID, max(GS) as MaxSal,min(GS) as MinSal from tblEmp_Info AS A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where IsInactive=0 and A.DeptId=4
group by B.DeptName,A.DeptId,D.DesigName,A.DesigID,GS
order by A.deptid,A.desigid,GS desc

Posted 8-Apr-17 21:15pm

Ramesh Kumar Barik

Updated 8-Apr-17 21:23pm

v2

Comments

Glorious007 9-Apr-17 6:26am

i see here is MaxSal and MinSal column both are same show. i need individual maximum and minimum code.
such as: empid DeptName designame gs
5 Garments Ass.Enginer 20,000(Max)
200 Garments Sr. Engineer 30,000(Max)

empid DeptName designame gs
20 Garments Ass.Enginer 7,000(Min)
45 Garments Sr. Engineer 12,000(Min)

Ramesh Kumar Barik 9-Apr-17 7:35am

Is one employee having multiple designation?
Can you pls share the some data of your tables in scree shot?

Glorious007 9-Apr-17 8:30am

one id for one designation, here is group Department, and designation
here i can't show screen shot. please give me your email id.

Glorious007 9-Apr-17 8:49am

now solve my problem. Many Many Thanks Ramesh Kumar Barik .

WITH cteRowNum AS (
SELECT B.DeptName,A.DeptID,D.DesigName,A.desigid, EmpName, GS,
DENSE_RANK() OVER(PARTITION BY A.Desigid ORDER BY GS DESC) AS RowNum
FROM tblEmp_Info As A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where A.DeptId=4 and A.IsInactive=0
)
SELECT DeptName,DesigName, EmpName, GS
FROM cteRowNum
WHERE RowNum = 1;

Ramesh Kumar Barik 9-Apr-17 9:00am

Good to know problem solved.. :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-04-09T02:02:00

Solution 2

Check this: sql - Highest Salary in each department - Stack Overflow[^]

You have to change it to your needs:

SQL

WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary
    FROM cteRowNum
    WHERE RowNum = 1;

Posted 9-Apr-17 2:02am

Maciej Los

Comments

Glorious007 9-Apr-17 8:47am

ohhhh nice!
i got it

WITH cteRowNum AS (
SELECT B.DeptName,A.DeptID,D.DesigName,A.desigid, EmpName, GS,
DENSE_RANK() OVER(PARTITION BY A.Desigid ORDER BY GS DESC) AS RowNum
FROM tblEmp_Info As A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where A.DeptId=4 and A.IsInactive=0
)
SELECT DeptName,DesigName, EmpName, GS
FROM cteRowNum
WHERE RowNum = 1;

Maciej Los 9-Apr-17 13:55pm

You're very welcome!
Cheers,
Maciej