Extracting Maximum Call History 1

[abrewis]

I have a view which produces three columns of data

Call_Number History Current_Group
1 1 5
1 2 8
1 3 6
2 1 6
2 2 6
3 1 8
3 2 9

I need to extract the call numer, the group and the history from the call's maximum row.

ie. Results should show

Call_Number History Current_Group
1 3 6
2 2 6
3 2 9

I have tried using serveral function without any luck.
Any help would be much appreciated.

Alasdair

 

[SQLDenis]

This should do it
select t1.call_number,t1.history,t1.current_group
from table t1 join (
select call_number,max(history) as MaxHistory from
table group by call_number) t2 on t1.number = t2.number
and t1.history =t2.MaxHistory

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[amrita418]

There are several ways to do this. One such idea would be:

SELECT 		call_number,
		History,
		Current_Group
FROM		#TableA a,
		(SELECT 	call_number, 
				MAX(History) max_history
		 FROM		#TableA
		 GROUP BY	call_number) b
WHERE 		a.call_number = b.call_number
		and a.history = b.max_history

PS: Code Not Tested

Regards,
AA

 

[abrewis]

Both work a treat, thank you very much.
Alasdair