SQL View with TOP Clause

[ljwilson]

I need some help. Here is the situation, I need to only show the record with the highest activity level from the CHAOS_Awards table. In the CHAOS_Awards table, they can be several records for each award_fk. That table has a column for ActivityNumber which increments for each record of a specific award_fk. How can get the view to just return the one with the highest ActivityNumber?

Here is the current SQL for the view:

SELECT     ca.Mnemonic, sv.award_fk, pp.visit_fk, pp.AccountNumber, SUM(pp.quantity * p.UnitPrice) AS totalVisitCost, pc.amount AS pendingChangeTotal, 
                      sv.subject_fk, ISNULL(st.FirstName, '') + ' ' + ISNULL(st.MiddleInitial, '') + ' ' + ISNULL(st.LastName, '') AS participantName, sv.is_reconciled
FROM         dbo.Procedures_Performed AS pp INNER JOIN
                      dbo.ProceduresPermitted_tbl AS p ON p.ProcedureID = pp.procedure_fk INNER JOIN
                      dbo.Scheduled_Visits AS sv ON sv.id = pp.visit_fk INNER JOIN
                      dbo.Subjects_tbl AS st ON sv.subject_fk = st.SubjectID LEFT OUTER JOIN
                      CHAOS.dbo.CHAOS_Awards AS ca ON ca.AwardNumber = sv.award_fk AND ca.Mnemonic IS NOT NULL LEFT OUTER JOIN
                      dbo.Reconciliation_Pending_Changes AS pc ON pc.visit_fk = sv.id AND pc.completeDate IS NULL
WHERE     (sv.award_fk <> 1865)
GROUP BY ca.Mnemonic, sv.award_fk, pp.visit_fk, pp.AccountNumber, pc.amount, sv.subject_fk, ISNULL(st.FirstName, '') + ' ' + ISNULL(st.MiddleInitial, '') 
                      + ' ' + ISNULL(st.LastName, ''), sv.is_reconciled

Thanks in advance.

 

[markros]

The view code is a bit hard to digest, so I will point you to the general ideas of how to solve such problem

http://forums.asp.net/t/1423102.aspx

 

[willdevelope]

Well since you want the max for a specific column try the Max function and leave everyhting else the same

Example

Max(activitynumvber) as Highest,***Just replace the field***