I have a stored procedure that pulls data for a SS Report. I need to include a Row Number column but I have never used RANK before and I'm gettig this error: Column 'WL.SSN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. Can anyone help me with the syntax? Here is what I have so far.
SELECT WL.SSN, WL.LastName, WL.FirstName, WL.AddressLine1, WL.AddressLine2, WL.AddressLine3, WL.City, WL.State, WL.ZipCode, WL.Plus4, S.SeminarID, S.SeminarType, RANK = Count(*) FROM @tblWaitList WL INNER JOIN @tblSeminar S ON WL.SeminarGuid=S.SeminarGuid WHERE S.SeminarID = @SeminarID AND S.SeminarType = @SeminarType AND RowNumber = COUNT(*) ORDER BY RANK
I have tried almost every approach to this from <a href="http://support.microsoft.com/kb/186133" rel="nofollow">http://support.microsoft.com/kb/186133</a> but I'm missing something.
Here is an example of what I'm trying to acheive...
Rank Au_Lname Au_Fname ---- -------------- ----------- 1 Bennet Abraham 2 Blotchet-Halls Reginald 3 Carson Cheryl 4 DeFrance Michel 5 del Castillo Innes 6 Dull Ann 7 Greene Morningstar 8 Green MarjorieAnswer1:
This should do:
SELECT rank() OVER (ORDER BY WL.LastName, WL.FirstName) as rank, WL.LastName, WL.FirstName FROM @tblWaitList WL INNER JOIN @tblSeminar S ON WL.SeminarGuid=S.SeminarGuid WHERE S.SeminarID = @SeminarID AND S.SeminarType = @SeminarType ORDER BY rank ;