Can anyone help with this query?

Cant work out the following:

Say i have a table 'P_STABILITY_PROTOCOL' that has the following data:

REC_ID ** LAST_AUTHORISED_VERSION_I ** LINKED_PSP_ID_I

1 1 83
2 2 83
3 3 83
4 1 108
5 1 114
6 2 114

I need the max LAST_AUTHORISED_VERSION_I value for each LINKED_PSP_ID_I group so i'm using the following query which returns part of what i need:

*********************************************************************************************************************************

SELECT MAX(LAST_AUTHORISED_VERSION_I) AS LAST_AUTHORISED_VERSION_I,LINKED_PSP_ID_I FROM(

SELECT

P_STABILITY_PROTOCOL.*,

R_PROTOCOL_TYPE.DESCRIPTION_T AS PROTOCOL_TYPE_T,

Z_STATUS.DESCRIPTION_T AS STATUS_T

FROM P_STABILITY_PROTOCOL, Z_STATUS, R_PROTOCOL_TYPE

WHERE (P_STABILITY_PROTOCOL.ZS_ID_I = Z_STATUS.ZS_ID_I)

AND (P_STABILITY_PROTOCOL.RPT_ID_I = R_PROTOCOL_TYPE.RPT_ID_I)

AND (P_STABILITY_PROTOCOL.SSMP_ID_I = 37)

AND (P_STABILITY_PROTOCOL.RETIRED_FLAG_T = 'N')

AND (Z_STATUS.DESCRIPTION_T = 'Approved')

ORDER BY LINKED_PSP_ID_I, LAST_AUTHORISED_VERSION_I)

GROUP BY LINKED_PSP_ID_I

*********************************************************************************************************************************

This returns the correct values of:

LAST_AUTHORISED_VERSION_I ** LINKED_PSP_ID_I

3 83
1 108
2 114

This is i need the REC_ID value (and others) returning as well but obviously if i add it to the SELECT bit of the original query then the 'Group by' will all go to *** and it'll just return every value in the DB not just the highest authorised value for the group. Anyone know how i do this? Could i use a cursor and loop through the table? Tried using self joins but just cannot get it to return the same values as the original query. That LAST_AUTHORISED_VERSION_I and LINKED_PSP_ID_I value combined will always be particular to a single distinct record if thats any help?

Cheers.

Check if this gives you a direction:

Declare @.ttable (recidint, versionidint, pspidint)insert into @.tselect 1 , 1 , 83unionallselect 2 , 2 , 83unionallselect 3 , 3 , 83unionallselect 4 , 1 , 108unionallselect 5 , 1 , 114unionallselect 6 , 2 , 114select T1.recid, T2.MAxVerId, T1.pspidfrom @.t T1Join (Select max(versionid)as MAxVerId, pspidfrom @.tgroup by pspid) T2on T1.versionid = T2.MAxVerIdand T1.pspid = T2.pspidOrder by recid