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
