sql server2008 select statement

Hello @Abed you can this method.

 

SELECT * INTO #Temp FROM (Select emp_no,emp_name,branch_name,A.Subject,MaxMark 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_no,emp_name,branch_name,A.Subject,MinMark 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.................

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

 

hello

where is the salary column in this table