sql server2008 select statement

Question

how to find max salary and min salaryemployee nameand department name suppose we have the following database table emp_no emp name branch name subject mark 4101 jhon a1 mathmatex 82.97 4122 peter a1 mathmatex 83.09 4123 nelson a1 physics 89.09 4129 suzan b1 mathmatex 90.00 4125 fredrek a1 physics 99.00 i need the result as emp_no emp name branch name subject max emp_no emp name branch name subject min 4129 suzan b1 mathmatex 90 4101 jhon a1 mathmatex 82.97 4125 fredrek a1 physics 99 4123 nelson a1 physics 89.09

Upendra Pratap Shahi · Answer

select * from ( select top 1 emp_no  emp_name  branch_name  subject  mark as max from #yourtable Order by mark desc ) b  ( select top 1 emp_no  emp_name  branch_name  subject  mark as min from #yourtable Order by mark ) c

Jignesh Trivedi · Answer

hi try following SQL query select * from ( select top 1 emp_no emp_name branch_name subject mark as max from #table Order by mark desc) b (select top 1 emp_no emp_name branch_name subject mark as min from #table Order by mark) c hope this will help you.

Pankaj Kumar Choudhary · Answer

Hello @Abed you can this method. SELECT * INTO #Temp FROM (Select emp_noemp_namebranch_nameA.SubjectMaxMark From EmpTab A INNER JOIN (Select Subject MAX(Mark)As MaxMark From EmpTab Group By Subject) B ON A.Subject = B.Subject AND A.Mark = B.MaxMark )C SELECT * INTO #Temp1 FROM (Select emp_noemp_namebranch_nameA.SubjectMinMark From EmpTab A INNER JOIN (Select Subject Min(Mark)As MinMark From EmpTab Group By Subject) B ON A.Subject = B.Subject AND A.Mark = B.MinMark )C SELECT * FROM #Temp t INNER JOIN #Temp1 t2 ON t.Subject=t2.Subject DROP TABLE #Temp DROP TABLE #Temp1 You can replace highlighted text with your table name. I know this method is very large but answer as you expect can't achieve with a single statement query. For better use You can create a stored procedure.................

Manoj Bhoir · Answer

Hi Try this query : Select * From YourTableName A INNER JOIN ( Select Subject  MAX ( Mark ) As Mark From YourTableName Group By Subject ) B ON A . Subject = B . Subject AND A . Mark = B . Mark

abed ahmad · Answer

thanx for your response by mistake it is mark insted of salary

ravi rawat · Answer

hello where is the salary column in this table

emp_no	emp name	branch name	subject	max	emp_no	emp name	branch name	subject	min
4129	suzan	b1	mathmatex	90	4101	jhon	a1	mathmatex	82.97
4125	fredrek	a1	physics	99	4123	nelson	a1	physics	89.09